Trouble filtering with multiple fields

[Anonymous]

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.

[JosefPrakljacic]

Hi @Anonymous ,

you should create a date table and use its columns for slicing.

Here is a explanation how to create it:

https://kohera.be/blog/power-bi/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/

https://www.youtube.com/watch?v=gyvhM5eiT0U

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[Anonymous]

I'm sorry, this does't work at all.

This just moves the conflict from the Code field to the Date field, with the same issue.

"You can't create a direect active relationship between Table 1 and Table 2, becuase that wold create ambiguity between Date and Table 1" 

[JosefPrakljacic]

Hi,

may I ask you to provide some sample data? So that we don't need to guess.

BR,

Josef

[Anonymous]

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.

[v-lid-msft · Accepted solution]

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.

[Anonymous]

Wow, fantastic, thanks!