Forum Discussion
Removing dependencies on tables
Hi,
Still learning the tricks here.
I have a situation that I haven't been able to figure out.
I have a slicer and 2 tables.
The slicer is simply a range of days.
The 1st table is showing the list of items sold on that day.
The 2nd table is showing the list of items sold on the same day a 364 days ago.
I was able to show both tables, the issue is the 2nd table is only showing items that were sold in table 1, and dismiss other items not in table 1. How do I list all items sold in T-364 days regardless whether the same item was sold in T-day?
Slicer: T-day
Table 1:
Item A
Item B
Item C
Item D
Table 2: (T-364 days)
Item A
Item B
Item D
Item E (this item is not showing because none in table 1).
Seems like pretty common situation, but I can't find the solution.
Please help.
Thanks,
EA
hi, Anonymous
For your data model, you need a dim Item table that contains all the Items both in table1 and table2.
You could try this formula to create this dim item table as below:
Dim Item= DISTINCT( UNION( VALUES(Table1 [Item]), VALUES(Table2 [Item])))
Then create a relationship with table1 and table2 by Item column.
Now when creating the visual, please use Item field from Dim Item table.
Best Regards,
Lin
1 Reply
v-lili6-msftCommunity Support
hi, Anonymous
For your data model, you need a dim Item table that contains all the Items both in table1 and table2.
You could try this formula to create this dim item table as below:
Dim Item= DISTINCT( UNION( VALUES(Table1 [Item]), VALUES(Table2 [Item])))
Then create a relationship with table1 and table2 by Item column.
Now when creating the visual, please use Item field from Dim Item table.
Best Regards,
Lin