Filter based on two columns

[Anonymous]

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!  

[amitchandak · Accepted solution]

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

[AlB · Accepted solution]

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 

[AlB]

@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 

[AlB · Accepted solution]

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 

[amitchandak · Accepted solution]

@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 · Accepted solution]

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