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

Hello, I think these measures will work.  Please note that I haven't tested these in Power BI.

 

Time at highest crime =
CALCULATE (
VALUES ( Table[Hour] ),
TOPN ( 1, ALL ( Table[Hour] ), [Crime Count Measure], 0 )
)

and 

 

Time at lowest crime =
CALCULATE (
VALUES ( Table[Hour] ),
TOPN ( 1, ALL ( Table[Hour] ), [Crime Count Measure], 1 )
)

 

The last parameter of TOPN() says which way to sort.  0 = descending and 1 = ascending.

 

So you're saying return the value of the Hour column, using CALCULATE to modify the filter context to return a 1 row table of Hour sorted by [Crime Count].

 

Hope this helps.

 

~ Chris H

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous

I tried this but the card shows sum of all times like 14.4K and when I select MAX on values it shows 23:00 which is the maximum hour of day. The idea behind formula seems perfect like sorting asc and desc based on crime counts and getting the corresponding time but it won't work as expected.

 

Please advise if anything comes to you.

 

Thanks!

Harshad

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