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

 

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!

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous,

     

    This might be happening because they are more than one time with same number of counts i.e. minimum count
    Time 18 and Time 16

  • 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

     

    One more observation:- It doesn't show value when crime is 0. But it shows the value for 1 i.e. when crime is 1 at 18 and 1 at 16 so it shows 1816. Actually it should show value for 0 and not 1. Also the min function is not working when all crimes are selected and works when one crime selected as above case.

     

    Do you have any further idea for this? Please advise.

     

    Thanks!

  • 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!