Forum Discussion

BJ_TS's avatar
BJ_TS
Regular Visitor
3 years ago
Solved

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

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.