The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I would like to know how can I get a specific value from the M side of a 1:M table based on a specific condition? With the sample dataset below:
WOID (FK) | FIID | Type | InvoiceDate | |||
123456 | F00001 | PPD | 1/1/2023 | |||
123456 | F00002 | TRM | 1/12/2023 | |||
123457 | F00003 | PPD | 2/1/2023 | |||
123457 | F00004 | PPD | 2/12/2023 | |||
123458 | F00005 | TRM | 3/1/2023 | |||
123458 | F00006 | PPD | 3/12/2023 |
How would I be able to get the following output as a calculated column? (If at least 1 related is Type = TRM, use the data from the TRM row, otherwise, use the most recent of the Type = PPD entry)
WOID | CalculatedInvoiceDate | |
123456 | 1/12/2023 | |
123457 | 2/12/2023 | |
123458 | 3/1/2023 |
I only know how to get the most recent date of the related table's entry using:
Most Recent InvoiceDate = CALCULATE(MAX('Revenue'[InvoicedDate]))
Solved! Go to Solution.
Hi,
Most Recent Invoice Date = COALESCE ( CALCULATE ( MAX (Revenue[InvoiceDate] ), Revenue[Type] = "TRM" ), CALCULATE ( MAX (Revenue[InvoiceDate] ) ) )
Hi,
Most Recent Invoice Date = COALESCE ( CALCULATE ( MAX (Revenue[InvoiceDate] ), Revenue[Type] = "TRM" ), CALCULATE ( MAX (Revenue[InvoiceDate] ) ) )
Hi @olimilo,
It depends on the expected outcome you'd like to see.
Is it 3 rows with FIID i/o WOID and the same dates?
I think I got it. Say if I had a dataset like so:
WOID (FK) | FIID | Type | InvoiceDate | |||
123456 | F00001 | PPD | 1/1/2023 | |||
123456 | F00002 | TRM | 1/12/2023 | |||
123456 | F00003 | PPD | 1/15/2023 | |||
123457 | F00004 | TRM | 2/1/2023 | |||
123457 | F00005 | TRM | 2/12/2023 | |||
123458 | F00006 | PPD | 2/28/2023 | |||
123458 | F00007 | PPD | 3/1/2023 | |||
123458 | F00008 | PPD | 3/12/2023 |
For me to get the FIID (or Type) of the WOID group with the earliest Invoice Date:
Hi @barritown, this works. If I were to get the FIID from the M-side table, which function would I be using in CALCULATE?
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |