Forum Discussion
Calculated Table
Hi, I need help creating a calculated table. Here is the scenario -
The requirement is to create a calculated table based the columns from 2 tables based on selection for the latest date from the second table.
Table 1 -
| country | rate | date |
| MX | .5 | 03/10/2020 |
| MX | .8 | 03/11/2020 |
| US | .7 | 03/10/2020 |
| US | .9 | 03/11/2020 |
Table 2-
| date |
| 03/11/2020 |
| 03/10/2020 |
Here is the expected result table.
| country | rate | date |
| MX | .8 | 03/11/2020 |
| US | .9 | 03/11/2020 |
Thanks for the help in advance.
Hi,
According to your further description, i create a seperate date slicer table:
Then try this measure:
Measure = IF(MAXX(ALL('Table 1'),'Table 1'[date])<>SELECTEDVALUE('Date Slicer'[Date]),IF(MAX('Table 1'[date])=MAX('Table 2'[Date]),1,0),IF(MAX('Table 1'[date])=SELECTEDVALUE('Date Slicer'[Date]),1,0))Apply this measure to the table1, when you choose the max date in table1, it shows the max date's data:
When you choose other date which is not equal to the max date in table1, it shows the data with table1[date]=table2[date]:
Here is my test pbix file:
Expect your reply!
Best Regards,
Giotto Zhi
10 Replies
- amitchandak
Super User
What is the role of second table
Try like
Measure =
VAR __id = MAX ( 'Table'[country] )
VAR __date = CALCULATE ( MAX( 'Table'[date] ), ALLSELECTED ( 'Table' ), 'Table'[country] = __id )
RETURN CALCULATE ( max ( 'Table'[rate] ), VALUES ( 'Table'[country] ), 'Table'[country] = __id, 'Table'[date] = __date )- v-gizhi-msft
Community Support
Hi,
Do you mean that the table 2 has more than one date, and when you choose one date in table2[date] slicer, it will show the expected result you posted？
If so, please try this measure:
Measure = IF(MAX('Table 1'[date])=SELECTEDVALUE('Table 2'[Date]),1,0)Apply this measure to the visual, when you select one date in slicer, it shows:
If you only have one date in your initial table 2, the first way in my original reply can solve it.
If you still have any issue, please for free to let me know.
Best Regards,
Giotto Zhi
- v-gizhi-msft
Hi,
Please try to create this measure:
Measure = IF(MAX('Table 1'[date])=MAX('Table 2'[date]),1,0)Then apply it to the table1 visual, the result shows:
Or if you want to generate a new table, please try this:
Table = FILTER(SELECTCOLUMNS('Table 1',"country",'Table 1'[country],"rate",'Table 1'[rate],"date",'Table 1'[date]),[Measure]=1)The result shows:
Tips: All above two ways do not need to create any relationship among tables.
Here is my test pbix file:
Hope this can help.
Best Regards,
Giotto Zhi
