Problem counting number of rows when using Filter with AND-logic

BJ_TS — Wed, 17 May 2023 06:49:30 GMT

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

Re: Problem counting number of rows when using Filter with AND-logic

Anonymous — Fri, 19 May 2023 03:16:57 GMT

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.

topic Problem counting number of rows when using Filter with AND-logic in DAX Commands and Tips

Problem counting number of rows when using Filter with AND-logic

Re: Problem counting number of rows when using Filter with AND-logic