Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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]))
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
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]) ))
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
@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]))
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]) ))
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |