Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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])
User | Count |
---|---|
64 | |
27 | |
25 | |
17 | |
11 |