Forum Discussion
Advocate IIDAX Assistance Required
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?
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
7 Replies
- Jihwan_Kim
Super User
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] ))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
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
Wendeley-NorthResolver I
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
