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 2am since count is 170 and min time 20.00 since min crime is 24 on that time.

 

 

Could you please advise how do I achieve this? I am thinking to make a column using DAX but not sure the formula in DAX.

 

Thanks!

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

21 Replies

  • 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

    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!