Count frequency of measure with respect to column

[Speedbird]

I have data where i, y and value are columns, and I've made measures max_val to find maximum of value across y for each i and y_max_val to identify the y where the maximum value occurred.

i	y	value	[max_val]	[y_max_val]
1	2020	0.96	0.96	2020
1	2025	0.6	0.96	2020
1	2030	0.06	0.96	2020
2	2020	0.77	0.79	2030
2	2025	0.3	0.79	2030
2	2030	0.79	0.79	2030
3	2020	0.63	0.63	2020
3	2025	0.02	0.63	2020
3	2030	0.17	0.63	2020
4	2020	0.93	0.93	2020
4	2025	0.52	0.93	2020
4	2030	0.86	0.93	2020
5	2020	0.9	0.9	2020
5	2025	0.55	0.9	2020
5	2030	0.44	0.9	2020

How do I write a measure to count the number of times y has the maximum value?

I'd like something like:

2020	4
2025	0
2030	1

[Icey · Accepted solution]

Hi @Speedbird ,

Create a measure like so:

count =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[i] ) + 0,
    FILTER ( 'Table', 'Table'[y] = [y_max_val] )
)

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[harshnathani]

HI @Speedbird ,

You will need to create a Calculated Column

Value equal Max Val =
IF (
    'Table'[value] = 'Table'[max_val],
    1,
    0
)

Then create a measure

Count of y and max_Val =
COALESCE (
    CALCULATE (
        COUNT ( 'Table'[i] ),
        'Table'[Value equal Max Val] = 1
    ),
    0
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[mahoneypat]

Please try this expression to get your desired result.

Count of Max =
VAR __summary =
    ADDCOLUMNS (
        SUMMARIZE ( iyTable, iyTable[y], iyTable[i] ),
        "ivalue", CALCULATE ( MAX ( iyTable[value] ) ),
        "imax", CALCULATE ( MAX ( iyTable[value] ), ALL ( iyTable[y] ) )
    )
RETURN
    COUNTROWS ( FILTER ( __summary, [ivalue] = [imax] ) ) + 0

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Speedbird]

Hi Pat,

Thanks for your answer! Unfortunately, it hasn't worked for me. The result I get from this measure is essentially the count of i for all years:

y	Count of Max
2020	5
2025	5
2030	5

Sorry, there is a bit more context, the table I showed in the OP is a filtered version of the full table, there are other ids in that table and also other years that page level slicers select for me.

[mahoneypat]

Since there are other columns in play, please see if this variation does the trick

Count of Max =
VAR __summary =
    ADDCOLUMNS (
        SUMMARIZE ( iyTable, iyTable[y], iyTable[i] ),
        "ivalue", CALCULATE ( MAX ( iyTable[value] ) ),
        "imax", CALCULATE ( MAX ( iyTable[value] ), ALLEXCEPT ( iytable, iyTable[i] ) )
    )
RETURN
    COUNTROWS ( FILTER ( __summary, [ivalue] = [imax] ) ) + 0

You could also try ALLSELECTED(iyTable) in place of the original ALL() as well.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Speedbird]

Thanks Pat, but ALLEXCEPT gives me count of i for all years, and ALLSELECTED gives me 1.