Forum Discussion
Measures, columns or parameters?
Hello, As a beginner Power BI user I have a configuration dilemma. I have 50 columns with data (values) and want to display the same 3 calculations applied on each column. These 3 calculations are:
1: Percentage of appearance of a specific number (20) in the column
2: Percentage of appearance of another number (14) in the column
3: Sum of the 2 percentages.
I would initially program 3 specific measures for each column, but I think it can be automated, right (if not this will be 150 different measures)? How can I write general measures, without having to specify each time the specific column? I understand that working with parameters I can filter the 3 calculations by the column that suits me, right?
can you help me please?
Kind regards.
Unpivot your data. Instead of 50 columns have two - attribute and value. Then you can write one generic measure, or maybe even solve the question through the user interface.
Hi WardWijngaert ,
According to your description, here's my solution.
Sample:
1.Create a field parameter containing all the columns.
2.Creat three measures:
Measure = VAR _Field = SWITCH ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "'Table'[Column1]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column1] = 20 ) ), "'Table'[Column2]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column2] = 20 ) ), "'Table'[Column3]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column3] = 20 ) ) ) RETURN DIVIDE ( _Field, COUNTROWS ( 'Table' ) )Measure2 = VAR _Field = SWITCH ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "'Table'[Column1]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column1] = 14 ) ), "'Table'[Column2]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column2] = 14 ) ), "'Table'[Column3]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column3] = 14 ) ) ) RETURN DIVIDE ( _Field, COUNTROWS ( 'Table' ) )Measure3 = [Measure] + [Measure 2]Put the parameter in a slicer, then get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Unpivot your data. Instead of 50 columns have two - attribute and value. Then you can write one generic measure, or maybe even solve the question through the user interface.
Hi WardWijngaert ,
According to your description, here's my solution.
Sample:
1.Create a field parameter containing all the columns.
2.Creat three measures:
Measure = VAR _Field = SWITCH ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "'Table'[Column1]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column1] = 20 ) ), "'Table'[Column2]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column2] = 20 ) ), "'Table'[Column3]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column3] = 20 ) ) ) RETURN DIVIDE ( _Field, COUNTROWS ( 'Table' ) )Measure2 = VAR _Field = SWITCH ( SELECTEDVALUE ( Parameter[Parameter Fields] ), "'Table'[Column1]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column1] = 14 ) ), "'Table'[Column2]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column2] = 14 ) ), "'Table'[Column3]", COUNTROWS ( FILTER ( 'Table', 'Table'[Column3] = 14 ) ) ) RETURN DIVIDE ( _Field, COUNTROWS ( 'Table' ) )Measure3 = [Measure] + [Measure 2]Put the parameter in a slicer, then get the correct result:
I attach my sample below for your reference.
Best regards,
Community Support Team_yanjiang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Great lbendlin & v-yanjiang-msft I tried both solutions and they work fine! Thank you very much for your help.
Hi WardWijngaert ,
If you don't have other problems, please kindly mark the helpful reply as solution, then we'll close the thread, other people who has the same problem will benefit here.
Best regards,
Community Support Team_yanjiang
