Forum Discussion
Count values that equal to Maximum per category.
Hello Jianpeng Li, thank you very much for your help.
It worked on the data I sent but it did not work on my original data.
I must have made a mistake in my original measure that I shared.
Anyway I was able to come up with a different approach that worked.
For anyone interested see measure below.
MEASURE =
VAR _max_table =
SUMMARIZE (
ALLSELECTED ( 'Table 2' ),
'Table 2'[category_1],
'Table 2'[category_2],
"max_evaluation", CALCULATE ( MAX ( 'Table 2'[evaluation] ) )
)
VAR _values =
SELECTCOLUMNS (
'Table 2',
"category_1", 'Table 2'[category_1],
"category_2", 'Table 2'[category_2],
"evaluation", 'Table 2'[evaluation]
)
VAR _inter =
INTERSECT ( _max_table, _values )
RETURN
COUNTROWS ( _inter )
Hi, dvavrous
Based on the PBIX file you provided, I created a new measure using the following DAX expression:
MEASURE =
VAR _seleted =
CALCULATE (
SELECTEDVALUE ( 'Table'[evaluation] ),
FILTER (
'Table',
CALCULATE (
MAXX (
SUMMARIZE (
ALLSELECTED ( 'Table' ),
'Table'[category_1],
'Table'[category_2],
"max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
),
[max_evaluation]
),
ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
) = 'Table'[evaluation]
)
)
RETURN
IF (
NOT ISINSCOPE ( 'Table'[category_2] ),
SUMX ( FILTER ( 'Table', 'Table'[evaluation] = _seleted ), [count_max] ),
COUNTX (
FILTER (
'Table',
CALCULATE (
MAXX (
SUMMARIZE (
ALLSELECTED ( 'Table' ),
'Table'[category_1],
'Table'[category_2],
"max_evaluation", CALCULATE ( MAX ( 'Table'[evaluation] ) )
),
[max_evaluation]
),
ALLEXCEPT ( 'Table', 'Table'[category_2], 'Table'[slicer] )
) = 'Table'[evaluation]
),
COUNT ( 'Table'[category_2] )
)
)
Here are the results:
I've uploaded the PBIX file I used this time below.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello Jianpeng Li, thank you very much for your help.
It worked on the data I sent but it did not work on my original data.
I must have made a mistake in my original measure that I shared.
Anyway I was able to come up with a different approach that worked.
For anyone interested see measure below.
MEASURE =
VAR _max_table =
SUMMARIZE (
ALLSELECTED ( 'Table 2' ),
'Table 2'[category_1],
'Table 2'[category_2],
"max_evaluation", CALCULATE ( MAX ( 'Table 2'[evaluation] ) )
)
VAR _values =
SELECTCOLUMNS (
'Table 2',
"category_1", 'Table 2'[category_1],
"category_2", 'Table 2'[category_2],
"evaluation", 'Table 2'[evaluation]
)
VAR _inter =
INTERSECT ( _max_table, _values )
RETURN
COUNTROWS ( _inter )
Hi, dvavrous
Thank you very much for your reply. Did the DAX expression you shared solve your current problem?Best Regards
Jianpeng Li