Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX for MIn/Max based on count

Hello All, Need hep!   I need to dispay (on card tile) min and max hour when the count of crime is min and max. Hour is on x axis and crime # on y axis. In this example I need to dispay max time as...
Show and Tell
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    Check this file here

     

    Try this

    Time_MaxCrime =
VAR MaxCount =
    MAXX ( VALUES ( Table1[Time] ), CALCULATE ( COUNT ( Table1[Crime Type] ) ) )
RETURN
    CALCULATE (
        CONCATENATEX (
            FILTER (
                VALUES ( Table1[Time] ),
                CALCULATE ( COUNT ( Table1[Crime Type] ) = MaxCount )
            ),
            Table1[Time]
        )
    )
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous,


You can try to use summarize function to group your data as same as current chart , then add a count column column to stored the count value. After these steps, you can direct use maxx/minx function to get data from above summarize table.

 

For example:

MAX count =
MAXX (
    SUMMARIZE ( Table, Table[Hour], "Count", COUNT ( Table[crime] ) ),
    [Count]
)

MIN count =
MINX (
    SUMMARIZE ( Table, Table[Hour], "Count", COUNT ( Table[crime] ) ),
    [Count]
)

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous

 

I used this formula but the card visual shows the count of crimes and not the actual time when crime is high and low. I did try many other ways but it won't work for me :(

 

Thanks!