topic Re: Calculated Table in Desktop

Calculated Table

Anonymous — Wed, 11 Mar 2020 03:40:48 GMT

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.

Re: Calculated Table

amitchandak — Wed, 11 Mar 2020 04:36:16 GMT

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 )

Re: Calculated Table

v-gizhi-msft — Wed, 11 Mar 2020 06:51:54 GMT

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

Re: Calculated Table

Anonymous — Wed, 11 Mar 2020 07:27:18 GMT

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?

Re: Calculated Table

v-gizhi-msft — Wed, 11 Mar 2020 08:23:21 GMT

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

Re: Calculated Table

Anonymous — Wed, 11 Mar 2020 10:44:03 GMT

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

Re: Calculated Table

Anonymous — Wed, 11 Mar 2020 11:24:01 GMT

bumping up..

Re: Calculated Table

Anonymous — Wed, 11 Mar 2020 12:22:16 GMT

Bumping up..

Re: Calculated Table

amitchandak — Wed, 11 Mar 2020 12:40:37 GMT

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.

Re: Calculated Table

v-gizhi-msft — Thu, 12 Mar 2020 02:32:00 GMT

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

Re: Calculated Table

Anonymous — Thu, 26 Mar 2020 15:09:56 GMT

Thank you!