DAX Assistance Required

[kagy100]

Hi Team,

Here is my query:

Table 1
Account	Financial Date	Financial Value
AB	1/05/2021	35
Table 2
Account	Consumption Date	Consumption value
AB	5/05/2021	23
Output Table
Account	Financial Date	Financial Value	Consumption Date	Consumption value
AB	1/05/2021	35	5/05/2021	23

Is the above possible in Dax? 

[Jihwan_Kim]

Hi, @kagy100 

Please check the below picture and the sample pbix file's link down below.

Output Table =

ADDCOLUMNS (

Table1,

"Consumption Date", LOOKUPVALUE ( Table2[Consumption Date], Table2[Account ], Table1[Account] ),

"Consumption value", LOOKUPVALUE ( Table2[Consumption value], Table2[Account ], Table1[Account] )

)

https://www.dropbox.com/s/sv4el51gwzltpod/kagy100.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[kagy100]

Thank You for your comprehensive answer Jihwan. I have attached my pbix file. could you kindly have a look and advise?

https://www.dropbox.com/s/kpy6rl29l9r96ry/PBIFORUM.pbix?dl=0

I get an error as "A table of multiple values was supplied where a single value was expected"

[Jihwan_Kim · Accepted solution]

Hi, @kagy100 

Thank you for your feedback.

Please check the link down below, and please check if I understood your question correctly.

I could not know which columns to show in the new table.

I suggest doing this in Power Query Editory.

https://www.dropbox.com/s/0szj5clsk16bc47/PBIFORUM.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[kagy100]

Thanks Jihwan_Kim. Ideally, my preference would be DAX but I guess Power Query also does the trick. 

[Wendeley-North]

Go to manage relationships:

Create a new relationship between the two tables, using [Account] as the identifying column.

Using Table 1 as the original table, create 2 new measures:

ConsumptionDate = RELATED ( Table2[Consumption Date] )

ConsumptionValue = RELATED ( Table2[Consumption Value] )

And use these as Values in Table1.

[kagy100]

Thank you for your kind time and reply. I was hoping to have an output table with the data instead of a measure. Would this be a possibility ? 

[Wendeley-North]

The proposed formulas for measures should work in a column, actually.

So: Go to Table1 > New Column > Use the two formulas above