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

Hi Anonymous

 

Following measure will get you the Time with Max CrimeCount.

See the attached file as well



Time_MaxCrime =
CALCULATE (
    VALUES ( Table1[Time] ),
    FILTER ( ALL ( Table1 ), Table1[Crimes] = MAX ( Table1[Crimes] ) )
)

Following measure will get you the Time with Min CrimeCount

Time_MinCrime =
CALCULATE (
    VALUES ( Table1[Time] ),
    FILTER ( ALL ( Table1 ), Table1[Crimes] = MIN ( Table1[Crimes] ) )
)

 

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