Forum Discussion
Helper IDAX formula or new table
Here is a simpler measure to do that (replace Records with your actual table name). To make it even more performant, it gets the leader and their count in one measure, and concatenate them together. Because it includes the Date, the "total" will show the value for the max day. Note this does not handle ties. It just returns one of the tied values. To handle ties, you could use concatenatex().
Leader-Count = VAR vSummary = SUMMARIZE ( Records, Records[ID], Records[Date], "Count", COUNTROWS ( Records ) ) VAR vTop1 = TOPN ( 1, vSummary, [Count], DESC ) RETURN MINX ( vTop1, Records[ID] ) & "-" & AVERAGEX ( vTop1, [Count] )Pat
Solution SageHere is a simpler measure to do that (replace Records with your actual table name). To make it even more performant, it gets the leader and their count in one measure, and concatenate them together. Because it includes the Date, the "total" will show the value for the max day. Note this does not handle ties. It just returns one of the tied values. To handle ties, you could use concatenatex().
Leader-Count =
VAR vSummary =
SUMMARIZE (
Records,
Records[ID],
Records[Date],
"Count", COUNTROWS ( Records )
)
VAR vTop1 =
TOPN ( 1, vSummary, [Count], DESC )
RETURN
MINX ( vTop1, Records[ID] ) & "-"
& AVERAGEX ( vTop1, [Count] )
Pat
Helper IPat,
One other question I was wondering if you could clarity. If I take your measure and apply it to a table visual, in addition to a slice based on ID, then I filter by an id that does not have a value in the table at all, the table visual still displays rows for each day, but the values are blank (as you can see the '-' still shows).
I created a "dimension" table with ID going from 1-7, but fact table only contains ID values up to 5.