Forum Discussion
Trouble filtering with multiple fields
Hi Anonymous ,
We can create a medium table to be the slicer.
1. create calculated table.
MonthTable = DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
CodeTable = DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))
2. create relation ship between four table
3. use the column in medium table as the silcer.
4. create measure using the source table.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Table 1
Code Date Revenue1 Revenue 2
Client A Jan $1000 -$500
Client B Jan $2000 $500
Client C Jan $1500 $50
Client D Jan $800
Client A Feb $1500 $800
Client B Feb $1000 $50
Client C Feb $100 $500
Client D Feb $800 $200
Tabel 2
Code Date Revenue_Planned1 Revenue_Planned2
Client A Jan $2000 $500
Client B Jan $1000 $1500
Client C Jan $1500 $500
Client D Jan $800 $200
Client A Feb $1500 $100
Client B Feb $1000 $50
Client C Feb $100 $500
Client D Feb $800 $200
So I want to see
Date Slicer: JAN
Code Slicer: Client C
Planned Revenue: $2000
Actual Revenue $1550
And to be able to change those 2 slicers to provide the data for one of more clinets and one or more dates.
Note that merging all possible dates and codes is not a tenable solution.
Community SupportHi Anonymous ,
We can create a medium table to be the slicer.
1. create calculated table.
MonthTable = DISTINCT(UNION(DISTINCT(Table1[Date]),DISTINCT(Table2[Date])))
CodeTable = DISTINCT(UNION(DISTINCT(Table1[Code]),DISTINCT(Table2[Code])))
2. create relation ship between four table
3. use the column in medium table as the silcer.
4. create measure using the source table.
BTW, pbix as attached.
Best regards,
Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Wow, fantastic, thanks!