Power BI - Measures to show a list of values | Forum

· High Priority · Version 365

Wed 21 Feb 2024, 16:29 Delegate Andres said...

Andres has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Power BI Modelling, Visualisation and Publishing course
Power BI DAX course

Power BI - Measures to show a list of values

Hi,

Could you please let me know how to get the results from the below measure4 in a column with each value in a single row instead of having all in a single cell?

I used CONCATENEX becuase it is the only way I can check the result.

If I just leave "ResultTable" after return, it does not work.

Measure4 =
VAR FilteredTable =
FILTER(
Skillko_Consolidation,
Skillko_Consolidation[Temporary Works Supervisor] >= TODAY()
)
VAR ResultTable =
SELECTCOLUMNS(
FilteredTable,
"TextValues",Skillko_Consolidation[Name & Surname]
)
RETURN
CONCATENATEX(ResultTable,[TextValues],", ")

Thank you in advance.

Mon 26 Feb 2024, 11:57 Trainer Jens said...

RE: Power BI - Measures to show a list of values

Hi Andres,

Thank you for the forum question.

I hope I understand what you want.

What about this column calculation:

Measure4 =If(Skillko_Consolidation[Temporary Works Supervisor] >= TODAY(),Skillko_Consolidation[Name & Surname])

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 26 Feb 2024, 13:35 Delegate Andres said...

RE: Power BI - Measures to show a list of values

Good Afternoon Jens,
Thank you for your response.

Please see below error message.
I have dates or empty values in column [Temporary Works Supervisor] and string in [Name & Surname].

Could you please review it and let me know how to make it work?

A single value for column 'Name & Surname' in table 'Skillko_Consolidation' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Thank you in advance,
Andres

Mon 26 Feb 2024, 14:11 Trainer Jens said...

RE: Power BI - Measures to show a list of values

Hi Andres,

Is the column in which you want the result in the Skillko_Consolidation table?

Do you use my IF?

If yes try:

Measure4 =If(Skillko_Consolidation[Temporary Works Supervisor] >= TODAY(),max(Skillko_Consolidation[Name & Surname]))

It will not return the right result but help me to understand the issue.

Blanks in your date column should not be a problem.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Training courses

	98.7 % recommend us
	More live stats...

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Power BI tip:

Query Editor Shortcuts

Ctrl + E: Open or close the Query Editor.
Ctrl + F: Find within the Query Editor.
Ctrl + H: Replace within the Query Editor.
Ctrl + D: Duplicate selected query.
Ctrl + ; (semicolon): Insert a step to create a custom column.
Ctrl + M: Enter the formula bar for the selected step.

View all Power BI hints and tips