Dax calculate number of occurrences with multiple filters

[Anonymous]

Hi all,

I've been scratching my head on this problem for a bit now and it officially has me stumped. I am trying to count the number of occurrences per person, based on Flag field. So if there is 2 instances of person A123, and only 1 of those are TRUE, then the occurrence value would be 1.

I've tried creating a calculated column with the below code but it doesn't seem to be performing the way I intend it to. When I look at the results I am getting occurrence values greater than the number of times a PersonKey even exists in the table. (I have ensured that do not summarize is selected for this calculated column)

Occurence =

COUNTX (

    FILTER ( 'Table', EARLIER ( 'Table'[PersonKey] ) = 'Table'[PersonKey] && 'Table'[Flag] = 1 ),

    'Table'[Flag]

)

Sample data:

PersonKey	Flag	Occurrence
A123	TRUE	1
A123	FALSE	1
A234	FALSE	0
A234	FALSE	0
B345	TRUE	2
B456	FALSE	0
B345	TRUE	2
B456	FALSE	0
C678	FALSE	2
C678	TRUE	2
C678	TRUE	2
C789	TRUE	1

[Barthel · Accepted solution]

Hey @Anonymous,

Try this code:

Occurence = 
CALCULATE ( 
    COALESCE ( COUNTROWS ( 'Table' ), 0 ),
    ALLEXCEPT ( 'Table', 'Table'[PersonKey] ),
    'Table'[Flag]
)

This formula counts the number of rows of the table, removing all filter context except the PersonKey; and where the flag is equal to 'true'. Blank values ​​are converted to 0 using COALESCE.

[Anonymous]

@Barthel

Thanks for the suggestion. Looking it over it seems like it would work but when I try it in my model it does not.

For example, the below should return a value of 1 for occurrence. However in my case it returns something that doesnt make sense, like 9. (I've checked to make sure no filters are being applied to the page or visual)

PersonKey	Flag	Occurrence
A	TRUE	9
A	FALSE	9
A	FALSE	9
A	FALSE	9

[Barthel]

Has your 'Flag' column the data type text instead op boolean? If so, that might be the problem. Use this then

Occurence =
CALCULATE (
    COALESCE ( COUNTROWS ( 'Table' ), 0 ),
    ALLEXCEPT ( 'Table', 'Table'[PersonKey] ),
    CONTAINSSTRING ( 'Table'[Flag], "true" )
)

[Anonymous]

No change unfortunately. The Flag field is boolean anyways so that didn't seem to be it.

[Barthel]

When I recreate your scenario in Power BI, I do get the expected result:

Could you perhaps send a screenshot of your table?

Just to make sure: a calculated column is static and is not affected by filters on your visuals and pages. If you want to calculate the Occurrence dynamically while taking filters into account, you can use a measure. 

[Anonymous]

Oh my goodness, no wonder it was "wrong". I was using it as a calculated column when i should have been using a measure. Good thought on calling that out. Thank you!

I'm very new to PBI and DAX so still learning the basics.