DAX for MIn/Max based on count

[harshadsp]

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!

[v-shex-msft]

Hi @harshadsp,

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

[harshadsp]

Hi @v-shex-msft

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!

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.

[Anonymous]

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

[harshadsp]

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]

Hi @harshadsp

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

[harshadsp]

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 · Accepted solution]

Hi @harshadsp

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

[harshadsp]

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]

Hi @harshadsp

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

[harshadsp]

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]

Hi @harshadsp,

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

[harshadsp]

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!

[harshadsp]

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!

[Zubair_Muhammad]

Hi @harshadsp

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"

[harshadsp]

Hi @Zubair_Muhammad

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

Thanks!

[Zubair_Muhammad]

Hi @harshadsp

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

[harshadsp]

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]

Could you share the file?

[harshadsp]

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!