cancel
Showing results for
Did you mean:

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

Resolver III

## Calculate table: Last record of day for that subscription_id (Dax)

Hello there. I would like to use DAX to calculate a new table from the below - basically retaining all the yellow rows.  The logic: For each subscription_id, keep its last record for the day (because that will tell me if it's currently on or off).  I've been struggling with this for hours, I'd really appreciate your help.

Input: this entire table

Output (via calculated table): Only the yellow rows

Logic: For each subscription_id, keep its last record for the day

1 ACCEPTED SOLUTION
Super User

Hi @michellepace
You may also try

Table 2 =
FILTER (
'Table',
'Table'[datetime]
= CALCULATE (
MAX ( 'Table'[datetime] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[subscription_id] )
)
)
3 REPLIES 3
Resolver III

Hello @tamerj1 and @OwenAuger . Thank you both very much for your reply. I ended up finding this little youtube video to show me how to do this in Power Query (without having to write code). I'm still really struggling with DAX, although @tamerj1 I could follow your code. And now that I have my (snapshot) table... I need to count the number of subcriptions and can't avoid DAX any longer. I'll post a new thread for this. Thank you very much once agian.

Super User

Hi @michellepace
You may also try

Table 2 =
FILTER (
'Table',
'Table'[datetime]
= CALCULATE (
MAX ( 'Table'[datetime] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[subscription_id] )
)
)
Super User

This should do the trick (existing table referred to as YourTable )

NewTable =
CALCULATETABLE (
YourTable,
INDEX (
1,
SUMMARIZE (
YourTable,
YourTable[subscription_id],
YourTable[Date],
YourTable[datetime]
),
ORDERBY ( YourTable[datetime], DESC ),
DEFAULT,
PARTITIONBY ( YourTable[Date], YourTable[subscription_id] )
)
)

Use INDEX to select the latest datetime per combination of subscription_id & Date.

Regards

Owen Auger
Blog

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.

#### 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