Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

@Anonymous , 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]))

 

View solution in original post

AlB
Super User
Super User

Hi @Anonymous 

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

Anonymous
Not applicable

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
Super User

@Anonymous 

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
Super User

Hi @Anonymous 

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

amitchandak
Super User
Super User

@Anonymous , 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]))

 

Anonymous
Not applicable

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]) ))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.