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

Thanks Zubair_Muhammad for your efforts. The link for attached file is not opening, could you please share it again?

Also when I see your data, you have counts of crimes for specific time 'Crimes' column itself but for me I need to use count function and I am not sure how to get the count in DAX itself.

 

I would need something like this -

 

Time_MaxCrime =
CALCULATE (
    VALUES ( Table1[Time] ),
    FILTER ( ALL ( Table1 ), Table1[Crimes] = MAX ( COUNT(Table1[Crimes]) ) ) --get the count here, but not sure how to write formula.
)

 

Could you please advise?

 

Thanks

Harshad

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
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]
        )
    )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    HI Anonymous

     

    Another way, in case there are'nt more than 1 time with same Count of Maximum Crimes

     

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

    My godness, this is so perfect! Thank you so much Zubair_Muhammad

     

    To get Time_MinCrime, I just replaced the MAXX with MINX in below formula but its not showing any value. Could you please provide me the folrmula to get the Min value as well?

     

    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]
            )
        )

     

    Thank you again!

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi Anonymous

       

      For Time with Min Count, try this

       

      Time_MinCrime =
VAR MinCount =
    MINX ( VALUES ( Table1[Time] ), CALCULATE ( COUNT ( Table1[Crime Type] ) ) )
RETURN
    CALCULATE (
        CONCATENATEX (
            FILTER (
                VALUES ( Table1[Time] ),
                CALCULATE ( COUNT ( Table1[Crime Type] ) = MinCount )
            ),
            Table1[Time]
        )
    )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Zubair_Muhammad

         

        Thanks for this but this Min formula is not working as expected. Initially its not showing any data but when I select any crime type its showing weired values like 1816, 1216 etc. The max formula is working perfectly like when no crime is selected then shwoing max time for all crimes and even one crime selected then showing its corresponding time. I used the following with the table names-

         

        Time_MinCrime =
        VAR MinCount =
            MINX ( VALUES ( CrimeDataAnalysisParsed[MeanHour] ), CALCULATE ( COUNT ( CrimeDataAnalysisParsed[ID] ) ) )
        RETURN
            CALCULATE (
                CONCATENATEX (
                     FILTER (
                          VALUES ( CrimeDataAnalysisParsed[MeanHour] ),
                          CALCULATE ( COUNT ( CrimeDataAnalysisParsed[ID] ) = MinCount )
                     ),
                      CrimeDataAnalysisParsed[MeanHour]
                   )
                )

         

        Please advise.

         

        Thanks!