Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
AccountID | Date | ActionCode | ActionQty |
AC1 | 6 Apr 2020 | AAA | 1 |
AC1 | 9 Apr 2020 | AAB | 1 |
AC1 | 7 Apr 2020 | BBB | 1 |
AC2 | 16 Apr 2020 | AAA | 1 |
AC2 | 20 Apr 2020 | AAB | 1 |
Expected results
Filter AccountID | Filter Date (mmm YYYY) | New measure expected result | Reason |
AC1 | Apr 2020 | 0 | Working days between AAA and AAB is > 2 |
AC2 | Apr 2020 | 1 | Working days between AAA and AAB is <= 2 |
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.
User | Count |
---|---|
41 | |
27 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
19 | |
18 | |
15 |