cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lenerius
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
amitchandak
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

AlB
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
AlB
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

AlB
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

amitchandak
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.