Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help with the complex DAX measure

I am struggling with a DAX measure. Here is the fact and 2 dimension tables I have:

 

 

 

 

factAction(dimAccountID, dimActionID, Date, ActionQty)
dimAction(dimActionID, ActionCode, ActionDescription)
dimDate(Date, WorkingDay)

 

 

 

 

The relationships are:

 

 

 

dimDate.Date -> factAction.Date
dimAction.dimActionID->factAction.dimActionID

 

 

 

And I have a DAX measure for ActionQty = SUM('factAction'[ActionQty])

We want to report on the number of actions of ActionCo

de AAA. Fine, that's easy. Just select ActionCode = AAA into the Power BI filter.

But now we want to also report, for those Accounts that had ActionCode AAA, the sum of ActionQty for ActionCode AAB where the ActionDate is within 2 working days of the AAA Action Date.

 

Any suggestions would be welcome!

 

Test data

AccountIDDateActionCodeActionQty
AC16 Apr 2020AAA1
AC19 Apr 2020AAB1
AC17 Apr 2020BBB1
AC216 Apr 2020AAA1
AC220 Apr 2020AAB1

 

Expected results

Filter AccountIDFilter Date (mmm YYYY)New measure expected resultReason
AC1Apr 20200Working days between AAA and AAB is > 2
AC2Apr 20201Working days between AAA and AAB is <= 2
3 REPLIES 3
Anonymous
Not applicable

Please check out the solution carefully.

 

Best

D

Anonymous
Not applicable

Hi. Please post some test data so that we can use it to create a solution. Best would be to have a PBI file but if you can't, then data in textual form will do. No pictures please. You can create tables here using the message's editing tools.

By the way, you've forgotten about the dimAccount table.

Best
D
Anonymous
Not applicable

Test data and expected results added to the original post.

No test data is needed for dimAccount, as nothing from that table is needed - simply the dimAccountID from factAction.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors