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

 

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

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