Forum Discussion
Filters used with Matrix Help
Could Someone help me with this challenge?
When setting the slicer to two of the selections ( BI developer and Data engineer ) I am still getting person 1014 as part of the table when this person has only one of these selections not both and I knew this is because the logic for slicer is OR not AND statement.
I thought the workaround will be to have the total skills part of the filter panel but the problem over there when I set the total skills is 2 then no data showing on my table!!!!
Table for sample data and snapshot for Power BI below.
| Person ID | Skill | State | First Name | Last Name |
| 1011 | BI developer | TX | David | Allen |
| 1011 | Data engineer | TX | David | Allen |
| 1011 | Project Manager | TX | David | Allen |
| 1011 | Power Automate | TX | David | Allen |
| 1012 | BI developer | WA | Jenn | AL |
| 1012 | Data engineer | WA | Jenn | AL |
| 1013 | Solution architect | TX | John | Randy |
| 1013 | IT support | TX | John | Randy |
| 1013 | Full Stack developer | TX | John | Randy |
| 1014 | Data Analytics | NY | Anna | Tom |
| 1014 | Data engineer | NY | Anna | Tom |
| 1014 | Report Developer | NY | Anna | Tom |
| 1015 | BI developer | WA | MO | Hanna |
| 1015 | Data engineer | WA | MO | Hanna |
| 1015 | Solution architect | WA | MO | Hanna |
| 1015 | IT support | WA | MO | Hanna |
| 1015 | Full Stack developer | WA | MO | Hanna |
Hi DavidAllen ,
Here are the steps you can follow：
1. Create measure.
Total Skills = COUNT('Table'[Skill])Count_Measure = CALCULATE(DISTINCTCOUNT('Table'[Skill]),ALLSELECTED('Table'))Flag = var _count=COUNTX(FILTER(ALLSELECTED('Table'),[Person ID]=MAX('Table'[Person ID])),[Skill]) return iF(_count =[Count_Measure],1,0)2. Place [Flag] in Filters, set is=1, apply filter.
3. Result:
After selection, 1014 is filtered out
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
Hi DavidAllen ,
Here are the steps you can follow：
1. Create measure.
Total Skills = COUNT('Table'[Skill])Count_Measure = CALCULATE(DISTINCTCOUNT('Table'[Skill]),ALLSELECTED('Table'))Flag = var _count=COUNTX(FILTER(ALLSELECTED('Table'),[Person ID]=MAX('Table'[Person ID])),[Skill]) return iF(_count =[Count_Measure],1,0)2. Place [Flag] in Filters, set is=1, apply filter.
3. Result:
After selection, 1014 is filtered out
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
DavidAllen , Try a measure like
measure =
var _cnt = countx(allselected(Fact_personalSkill), Fact_personalSkill[Skill])
return
sumx(filter(summarize(Fact_personalSkill, dim_person[person id], "_1", [Total Skills]), [_1] =_cnt) ,[_1])
