Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi - I have 2 tables, notes and payments on an account. I am trying to determine which accounts had a payment after the max note date. Here is the sample file: Sample File
Here is the notes table
Here is the payments table:
In this example, accounts 1 and 2 had a payment after the max note date and not account 3.
Here is my model. This is simplied, but I am dealing with millions of rows so ideally if this can be done in a measure that would be great. Appreciate the help!
Solved! Go to Solution.
Here is a measure that shows one way to do it.
Accts Late Payments =
VAR vSummary =
ADDCOLUMNS (
VALUES ( 'DIM: Account Numbers' ),
"cMaxNote",
CALCULATE (
MAX ( 'FACT: Notes'[Note Date] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
VAR vThisMax = [cMaxNote]
RETURN
CALCULATE (
COUNTROWS ( 'FACT: Payment' ),
'FACT: Payment'[Payment Date] > vThisMax
) >= 1
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Here is a measure that shows one way to do it.
Accts Late Payments =
VAR vSummary =
ADDCOLUMNS (
VALUES ( 'DIM: Account Numbers' ),
"cMaxNote",
CALCULATE (
MAX ( 'FACT: Notes'[Note Date] )
)
)
RETURN
COUNTROWS (
FILTER (
vSummary,
VAR vThisMax = [cMaxNote]
RETURN
CALCULATE (
COUNTROWS ( 'FACT: Payment' ),
'FACT: Payment'[Payment Date] > vThisMax
) >= 1
)
)
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
139 | |
113 | |
103 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |