cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## DAX formula or new table

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".

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

1 ACCEPTED SOLUTION
Solution Sage

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

Microsoft Employee
3 REPLIES 3
Solution Sage

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

Microsoft Employee
Helper I

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.

Helper I

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors