cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Filter based on two columns

Hi! 

 

I have a table that looks something like this:

 

Date

Responsible

Team leader

Amount

1/2/2020

A

A

50

1/2/2020

B

C

40

2/2/2020

B

C

20

2/2/2020

C

A

40

2/2/2020

A

B

80

 

I have a Table ”DimPerson” that I want to use for filtering:

 

Person

A

B

C

 

I would like to create a column chart that has Date on the x-axis and sum of Amount as value.

 

I would like to filter the column chart using DimPerson. For example if I filter ”A” i want all rows that has A in either column Responsible or Teamleader. And If I filter ”A” and ”B” I want all rows that has A or B in either Responsible or Teamleader.

 

Need help to figure this out! Grateful for all you help and advice!

Cheers!  

 

3 ACCEPTED SOLUTIONS
Super User IV
Super User IV

@Lenerius , if person is not join with any of the columns

 

Measure =
calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) && table[Team leader] in values(person[person]) ))

 

If joined with one of the column

 

Measure =
calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) && table[Team leader] in values(person[person]) ),all(person[person]))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Super User III
Super User III

Hi @Lenerius 

Without a relationship between the tables, create this measure and place it in the chart:

Measure = 
VAR selectedPerson_ = DISTINCT ( DimPerson[Person] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            ALL ( Table1[Responsible], Table1[Team leader] ),
            Table1[Responsible] IN selectedPerson_ || Table1[Team leader] IN selectedPerson_
        )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Thank you so much fo quick response!!! This worked perfectly!

 

I just changed "&&" to "| |" in the measure: calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) || table[Team leader] in values(person[person]) ))

View solution in original post

4 REPLIES 4
Super User III
Super User III

@Lenerius 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Super User III
Super User III

Hi @Lenerius 

Without a relationship between the tables, create this measure and place it in the chart:

Measure = 
VAR selectedPerson_ = DISTINCT ( DimPerson[Person] )
RETURN
    CALCULATE (
        SUM ( Table1[Amount] ),
        FILTER (
            ALL ( Table1[Responsible], Table1[Team leader] ),
            Table1[Responsible] IN selectedPerson_ || Table1[Team leader] IN selectedPerson_
        )
    )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

Super User IV
Super User IV

@Lenerius , if person is not join with any of the columns

 

Measure =
calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) && table[Team leader] in values(person[person]) ))

 

If joined with one of the column

 

Measure =
calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) && table[Team leader] in values(person[person]) ),all(person[person]))

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Thank you so much fo quick response!!! This worked perfectly!

 

I just changed "&&" to "| |" in the measure: calculate(sum(Table[Amount]) , filter(Table, table[Responsible] in values(person[person]) || table[Team leader] in values(person[person]) ))

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors