Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi. I have a table "PersonCertificates" with two columns:
Person Certificate
MrA Cert1
MrA Cert2
MrA Cert3
MrB Cert1
MrB Cert2
MrC Cert3
I have a slicer for Certificate and a table to list all Person(s).
With the help of the two measures below I can apply AND-logic so the table only lists all persons that have all the selected certificates:
_Number_of_selected_certificates := IF ( ISFILTERED ( 'PersonCertificates'[Certificate] ), COUNTROWS ( ALLSELECTED ( 'PersonCertificates'[Certificate] )), 0 )
_Person_Got_all_selected_certificates := IF ( [_Number_of_selected_certificates] = 0, 1, IF ( DISTINCTCOUNT ( 'PersonCertificates'[Certificate] ) = [_Number_of_selected_certificates], 1, 0 ))
I use {_Person_Got_all_selected_certificates is 1} as a filter on the table that lists all Person's and this works fine. If I select Cert1 and Cert3 in the slicer I only get Mr A in the list - as expected. But I also want to calculate the number of persons listed in the table and I can't get this to work. I use this measure to calculate rows:
Number of person :=
var vPersonTable = SUMMARIZE ( 'PersonCertificates', 'PersonCertificates'[Person], "Person Got All selected certificates", [_Person_Got_all_selected_certificates] )
return
CALCULATE ( COUNTROWS ( vPersonTable ), FILTER ( vPersonTable, [Person Got All selected certificates] = 1 ))
This measure returns 3 when I select Cert 1 and Cert 3 but only shows Mr A in the table.
How can i get this calculation to work?
Thanx!
//Henrik
Solved! Go to Solution.
Hi @BJ_TS ,
I have created a simple sample, please refer to it to see if it helps you.
CountNumber =
var _a = SELECTCOLUMNS('Table 2',"Certificate",[Certificate])
return CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER(ALL('Table'),[Certificate] in _a))
Name =
var _a = SUMMARIZE(ALL('Table'),[Person],"Flag",CALCULATE(COUNT('Table'[Certificate]),FILTER(ALL('Table'),[Certificate] in SELECTCOLUMNS('Table 2',"Certificate",[Certificate])&&[Person]=EARLIER('Table'[Person]))))
var _b = COUNTROWS(ALLSELECTED('Table 2'[Certificate]))
return MAXX(FILTER(_a,[Person]=SELECTEDVALUE('Table'[Person])&&[Flag]=_b),[Person])
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BJ_TS ,
I have created a simple sample, please refer to it to see if it helps you.
CountNumber =
var _a = SELECTCOLUMNS('Table 2',"Certificate",[Certificate])
return CALCULATE(DISTINCTCOUNT('Table'[Person]),FILTER(ALL('Table'),[Certificate] in _a))
Name =
var _a = SUMMARIZE(ALL('Table'),[Person],"Flag",CALCULATE(COUNT('Table'[Certificate]),FILTER(ALL('Table'),[Certificate] in SELECTCOLUMNS('Table 2',"Certificate",[Certificate])&&[Person]=EARLIER('Table'[Person]))))
var _b = COUNTROWS(ALLSELECTED('Table 2'[Certificate]))
return MAXX(FILTER(_a,[Person]=SELECTEDVALUE('Table'[Person])&&[Flag]=_b),[Person])
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
8 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
9 |