Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Trouble filtering with multiple fields

OK, so I have two tables of financial data:

Table 1
Code     Date      Revenue1     Revenue 2

 

Tabel 2
Code     Date      Revenue_Planned1       Revenue_Planned2

 

I want to slice both tables with one date clicer and one code slicerthat shows me the sum of (Revenue1 and Revenue 2) AND the sum of (Revenue_Planned1 and Revenue_Planned2) for the selected month(s), ideally in the KPI visual. (TO see how we are tracking revenue vs planned revenue)


But I can't add two active relationships.

 

There is a link between the date fields, and the Code fields, but only one can be active.

 

I added a seperate "Code" list for the codes (one line per) and did 1-many between that and the tables, nope, still couldn't work, as their is an indirect relationship (in other words, this doesn't help at all)

 

I tried the UseRelationbship function, but it's a mystery to me how that thing is supposed to work.

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

     

6 Replies