cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Yxalitis Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
v-lid-msft New Contributor
New Contributor

Re: Trouble filtering with multiple fields

Hi @Yxalitis ,

 

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.PNG

 

3. use the column in medium table as the silcer.

2.PNG

 

4. create measure using the source table.

4.PNG

 

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.

 

View solution in original post

6 REPLIES 6
JosefPrakljacic Established Member
Established Member

Re: Trouble filtering with multiple fields

Hi @Yxalitis ,

 

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

 

 

Yxalitis Frequent Visitor
Frequent Visitor

Re: Trouble filtering with multiple fields

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 Established Member
Established Member

Re: Trouble filtering with multiple fields

Hi,

 

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

 

BR,

Josef

Yxalitis Frequent Visitor
Frequent Visitor

Re: Trouble filtering with multiple fields

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 New Contributor
New Contributor

Re: Trouble filtering with multiple fields

Hi @Yxalitis ,

 

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.PNG

 

3. use the column in medium table as the silcer.

2.PNG

 

4. create measure using the source table.

4.PNG

 

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.

 

View solution in original post

Yxalitis Frequent Visitor
Frequent Visitor

Re: Trouble filtering with multiple fields

Wow, fantastic, thanks!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 273 members 3,079 guests
Please welcome our newest community members: