Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I have this Table1:
ID | Type | Status | Date |
123 | A | Ongoing | 01/04/2020 |
123 | B | Cancelled | 03/04/2020 |
And I want to calculate the 2,3,4th column of the Table2 below:
ID | Type | Status | Date |
123 | B | Cancelled | 03/04/2020 |
123 | B | Cancelled | 03/04/2020 |
So basically, I want for every ID in Table2[Col1], I want to search in Table2 and return the latest Type, the latest Status and the latest Date.
Please note that in every field of each of the tables above, there may be duplicates. I tried some formulas but I get a 'table returned instead of single value' error.
Any idea?
Thanks
Solved! Go to Solution.
Hi @UsePowerBI ,
I think the function that you used returns a table. You need to create a calculated table to return them all together. Or create formulas get the results one by one.
I create the measures. Please check if it is what you want.
Latest Date =
CALCULATE(
MAX('Table 1'[Date]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Latest Status =
CALCULATE(
MAX('Table 1'[Status]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Latest Type =
CALCULATE(
MAX('Table 1'[Type]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Hi @UsePowerBI ,
I think the function that you used returns a table. You need to create a calculated table to return them all together. Or create formulas get the results one by one.
I create the measures. Please check if it is what you want.
Latest Date =
CALCULATE(
MAX('Table 1'[Date]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Latest Status =
CALCULATE(
MAX('Table 1'[Status]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Latest Type =
CALCULATE(
MAX('Table 1'[Type]),
FILTER(
'Table 1',
'Table 1'[ID] = MAX('Table 2'[ID])
&& 'Table 1'[Date] = MAX('Table 1'[Date])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |