topic Re: Counting 2 columns from the same table in DAX Commands and Tips

Counting 2 columns from the same table

Crazypad — Wed, 06 Sep 2023 20:32:01 GMT

I am trying to create a measure that counts the "Attended" from the Attendance column and the "Complete" from the Interview Status__C column. I have a formula that pulls in the "Attended" totals but it doesn't add "Complete" totals":

Count Attended plus Completed =

COUNTX(

FILTER(

'Campaign Member',

'Campaign Member'[Attendance] = "Attended" && 'Campaign Member'[Interview_Status__c] = "Complete"

)

This is new to me so any help would be much appreciated.

TIA

Re: Counting 2 columns from the same table

DataNinja777 — Wed, 06 Sep 2023 21:26:50 GMT

Hi Crazypad

Your dax code produces intersecting part like below:

What about replacing double ampersand with or (||)?

Re: Counting 2 columns from the same table

Crazypad — Thu, 07 Sep 2023 14:33:11 GMT

Hi DataNinja777 - I want it to combine/add both "Attended" and "Complete" but with my original formula, it is only counting Attended.

Re: Counting 2 columns from the same table

DataNinja777 — Thu, 07 Sep 2023 21:08:51 GMT

Hi Crazypad

It sounds like your "Attended" field is a subset of "Complete" field, and everyone who "Attended" has "Completed" interview. Is this correct?

Replacing double ampersand ("&&") with double pipe ("||") counts "Complete" without double counting "Attended". Is this what you want?

Re: Counting 2 columns from the same table

Crazypad — Fri, 08 Sep 2023 16:51:16 GMT

Hi DataNinja777,

No, not always does a member attend and complete. So I want it to count Attended(which is a column that populates if a member attended a meeting) and Compete(which is a column that populates if a member completed an interview.

Hope that makes sense and thanks for your help!

Re: Counting 2 columns from the same table

DataNinja777 — Fri, 08 Sep 2023 23:02:55 GMT

Hi Crazypad ,

As an illustration where "Attended" has 5 people and "Complete" has 5 people, of which 2 are overlapping with each other, your initial formula with double ampersand will calculate, 2 (only the intersecting part), while my formula with double pipe will calculate 8 (5 + 5 - 2), where 2 is subtracted in order to avoid double counting of the overlapping part. In this illustration, what is your expected outcome? Is it 10 where each "Attended" and "Complete" is counted independently of each other like they are mutually exclusive?

Re: Counting 2 columns from the same table

jfern78 — Sat, 09 Sep 2023 21:41:21 GMT

maybe you can use something like this to count attended and complete

Measure = var _attended = COUNTX(FILTER('Campaign Member',

'Campaign Member'[Attendance] = "Attended"),

)

var _complete =

COUNTX(

FILTER(

'Campaign Member', 'Campaign Member'[Interview_Status__c] = "Complete"

)

return _attended + _complete

Re: Counting 2 columns from the same table

Crazypad — Mon, 11 Sep 2023 19:38:47 GMT

That worked perfectly.

Thank you!!!