Calculated Table

[Anonymous]

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. 

[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:

pbix 

Hope this can help.

Best Regards,

Giotto Zhi

[amitchandak]

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 )

[Anonymous]

The second table will have only one value ..My mistake.

Basically, select coulmns/records from table1 based on one date from table 2 

Table2-

date

03/11/2020

Does is change how we implement the DAX? 

[v-gizhi-msft]

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

[Anonymous]

Zhi, Thank you for your feedback so far. 

There is a small change .I should have been more specific from the begining.

We are taking the MAX of date from Table 1 but what if we have a pick a date that is not the MAX but is equal to the date from the Table 2.

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
MX	.8	03/20/2020
US	.7	03/20/2020

Table 2

Date

03/11/2020

Result Table -

country	rate	date
US	.9	03/11/2020
MX	.8	03/11/2020

[v-gizhi-msft · Accepted solution]

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:

pbix 

Expect your reply!

Best Regards,

Giotto Zhi

[Anonymous]

Thank you!

[Anonymous]

bumping up..

[Anonymous]

Bumping up..

[amitchandak]

@Anonymous ,

This should do

Max Rate =
var _max =maxx(Table2,Table2[Date])
return
calculate(max(Table1[Rate]), table1[Date]=_max)

Appreciate your Kudos.

Please mark one or more user @ from whom you are looking for an answer. That comes in mail notification as mention.