Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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] ) )
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
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!
Hi,
Share the link from where i can download your file.
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
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
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] ) ) )
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
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] ) )
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!
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] ) )
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!
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
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!
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!
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"
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], ", " ) )
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 !
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |