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

oh, you are correct, Zubair_Muhammad

 

Is there any way we could handle this situation? Could we show just one value in this case?

 

Please advise, thanks!

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Anonymous

 

How would you like to handle this?

 

1)we can choose between the earliest or latest time or

2)we can show both times as Text for example
"Time 11 & Time 18"

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Zubair_Muhammad

     

    We could display it with comma separated like "18, 16".

     

    Thanks!

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    See if this helps

     

    MinCrime =
VAR MinCount =
    MINX ( ALL ( Table1[Time] ), CALCULATE ( COUNT ( Table1[Crime Type] ) ) )
RETURN
    CALCULATE (
        CONCATENATEX (
            FILTER (
                ALL ( Table1[Time] ),
                CALCULATE ( COUNT ( Table1[Crime Type] ) ) = BLANK ()
                    || CALCULATE ( COUNT ( Table1[Crime Type] ) ) = 0
                    || CALCULATE ( COUNT ( Table1[Crime Type] ) = MinCount )
            ),
            Table1[Time],
            ", "
        )
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Zubair,

     

    This is so excellent that I don't know how to thank you! :) Only problem with this Min function that it doen't show any value initially i.e. when no crime is manually selected (that means all crimes are selected by default). It works fine when I manually select any one crime. But the MAX function is working perfectly in any case.

     

    Do you have any idea what could be the problem?

     

    I really appreciate all your efforts.

     

    Thank you Zubair !

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago
    Could you share the file?
  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Zubair_Muhammad

     

    I am not sure how can I share the file here. Also is there any way I can share file just to you? Please let me know.

     

    Thanks!