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.
Hi,
We are using power bi with ssas tabular model (connect live) option
we have below data:
Department Hour TransactionCount week
D1 1-2 pm 10 week1
D1 2-3 pm 20 week1
D1 3-4 pm 15 week1
D1 4-5 pm 20 week1
Report needed in below format
Department Max_Tran_Count count_occurence hour
D1 20 2 2-3 pm
need dax query for count_occurence
Any suggestion please
Solved! Go to Solution.
Perhaps:
count_occurence measure =
VAR __Max = [Max_Tran_Count]
VAr __Table =
SUMMARIZE(
'Table',
[Department],
[Hour],
[week],
"__TransactionCount",SUM([TransactionCount])
)
RETURN
COUNTROWS(FILTER(__Table,[__TransactionCount] = __Max))
Perhaps:
count_occurence measure =
VAR __Max = [Max_Tran_Count]
VAr __Table =
SUMMARIZE(
'Table',
[Department],
[Hour],
[week],
"__TransactionCount",SUM([TransactionCount])
)
RETURN
COUNTROWS(FILTER(__Table,[__TransactionCount] = __Max))
Oh, just use LOOKUPVALUE or MAXX(FILTER(...)...) for that. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/td-p/985814
See attached PBIX:
count_occurence =
VAR __Max = MAXX(ALLSELECTED('Table'),'Table'[TransactionCount])
RETURN
COUNTROWS(FILTER('Table',[TransactionCount] = __Max))
hour measure =
VAR __Max = MAXX(ALLSELECTED('Table'),'Table'[TransactionCount])
RETURN
MINX(FILTER('Table',[TransactionCount] = __Max),[Hour])
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.