DAX formula or new table

[sbuster]

I have a situation where my fact table contains many records for the same key, on the same day.  I would like to summarize the data by day and identify the key with the most entries (i.e. the leader per day).   See image below.

I'm able to generate the above summary table with the following query:

Rollup = GENERATE (

    VALUES ( 'Table'[Date] ),

    TOPN (

        1,

        CALCULATETABLE (

            GROUPBY (

                'Table',

                'Table'[ID],

                "Count", COUNTX ( CURRENTGROUP (), 'Table'[Rec ID] )

            )

        ),

        "Count"

    )

   )

And then add the 'Is Leader' measurement to the table indicating on a given day with a 1, which ID was the "leader".

Leader = IF(

        CALCULATE(

            MAX('Rollup'[Count]),

            ALLEXCEPT('Rollup','Rollup'[Date]))=MAX('Rollup'[Count]),1,0)

Using this measure as a filter on 'Leader=1' in any visual does the trick.

I admit I stiched this together via many different blogs, video and forum questions, but it works!  The problem is the performance is horrible.  Is there a better way to approach this?  Creating the Rollup table forces me to manage relationships to my Dimension tables (it works, but not desirable).  As mentioned, performance is bad!

Thanks

[ppm1 · Accepted solution]

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

[sbuster]

Pat,

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.

 

[sbuster]

Pat,

This works great and has much better performance.  If you don't mind, can I ask some clarifying question to make sure I understand what you've implemented...

1) The summarize function is fairly straight forward.  Group data by id/date... did you use summarize because it's more performant that groupby, personal preference??

2) Top1 will always return the top record recardless of filter context... so if I had no filter applied I get the leader for all time, if I have a day filter applied I get the daily leader.  If I have a ID selected then the top record for that 1 asset will be returned regardless of day.

3) I'm not sure I understand the use of MINX, especially on a table of 1.  What wouldn't you use MAX?
4) AVERAGEX of a table of 1 will simply return the same value.  Are you using because you have / want to use an iterator function, and as long as there is 1 record/value, average and/or sum would return the exact same value, true?

Thanks