The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.
Hi All,
I want to write a DAX expression to return the time from the maximum count of rows.
So as per below it would be 17:00.
My best attempt. This is returning the wrong answer at 19:00 currently
Solved! Go to Solution.
Try these measures. The concept is to rank [Count Rows] (a measure) in a virtual table, and then filter the virtual table for the top-ranked row.
Count Rows = COUNT ( 'Bike & Pedestrian Data'[ID] )
MaxTimeCount =
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE (
'Bike & Pedestrian Data',
'Bike & Pedestrian Data'[TimeExtract (bins)],
'Bike & Pedestrian Data'[Transport Type]
),
"@CountRows", [Count Rows]
)
VAR vRankTable =
ADDCOLUMNS (
vBaseTable,
"@Rank", RANKX ( vBaseTable, [@CountRows],, DESC, DENSE )
)
VAR vTopRank =
FILTER ( vRankTable, [@Rank] = 1 )
VAR vResult =
MAXX ( vTopRank, 'Bike & Pedestrian Data'[TimeExtract (bins)] )
RETURN
vResult
Proud to be a Super User!
Worked a charm thank you very much
Try these measures. The concept is to rank [Count Rows] (a measure) in a virtual table, and then filter the virtual table for the top-ranked row.
Count Rows = COUNT ( 'Bike & Pedestrian Data'[ID] )
MaxTimeCount =
VAR vBaseTable =
ADDCOLUMNS (
SUMMARIZE (
'Bike & Pedestrian Data',
'Bike & Pedestrian Data'[TimeExtract (bins)],
'Bike & Pedestrian Data'[Transport Type]
),
"@CountRows", [Count Rows]
)
VAR vRankTable =
ADDCOLUMNS (
vBaseTable,
"@Rank", RANKX ( vBaseTable, [@CountRows],, DESC, DENSE )
)
VAR vTopRank =
FILTER ( vRankTable, [@Rank] = 1 )
VAR vResult =
MAXX ( vTopRank, 'Bike & Pedestrian Data'[TimeExtract (bins)] )
RETURN
vResult
Proud to be a Super User!
User | Count |
---|---|
56 | |
54 | |
53 | |
47 | |
30 |
User | Count |
---|---|
175 | |
88 | |
69 | |
48 | |
46 |