Forum Discussion
How to display data from two tables (one table created using query editor)
Hi,
Try this:
- Create a Calendar Table and build a relationship from the Date column of the 2 Tables to the Date column of the Calendar Table
- Create another Table with unique Sales reps and build a relationship from the Sales rep column of the 2 Tables to the Sales rep column of the new Table
- To your slicer visual, drag the Sales rep column to the slicer
- To the Table visual, drag Date from the Calendar Table
- Write these measures
Actual sales = SUM(Sales[Sales])
Target sales = SUM(Target[Sales])
Hope this helps.
Hi,
Have you tried using the DAX Function LOOKUPVALUE(). You can add a new column in the Sales Table and use the Function like
LOOKUPVALUE(Target[Target], Target[Date], Sales[Date]). This should bring it in as a new column in the sales Table.
Else if you dont want to affect the sales Table, then Create a new Table with the Below Expression:
SUMMARIZECOLUMNS(Sales[Date], Sales[Sales],"Target",CALCULATE(MAX(Target[Target], TREATAS(VALUES(Sales[Date]), Target[Date))))
Basically, the TREATAS function would use the values of other Tables as the filter Values of the Current Table.
Thanks for the reply. will check revert soon.
Anonymous wrote:Hi,
Have you tried using the DAX Function LOOKUPVALUE(). You can add a new column in the Sales Table and use the Function like
LOOKUPVALUE(Target[Target], Target[Date], Sales[Date]). This should bring it in as a new column in the sales Table.
Else if you dont want to affect the sales Table, then Create a new Table with the Below Expression:
SUMMARIZECOLUMNS(Sales[Date], Sales[Sales],"Target",CALCULATE(MAX(Target[Target], TREATAS(VALUES(Sales[Date]), Target[Date))))
Basically, the TREATAS function would use the values of other Tables as the filter Values of the Current Table.