topic Re: Counting values in one column based on string filters from another column in DAX Commands and Tips

Counting values in one column based on string filters from another column

Anonymous — Fri, 13 Aug 2021 22:09:40 GMT

Hello,

I have a table name tableA with
date user id event

xx/xx/xxxx abcdef step 1

xx/xx/xxxx abcdef step 2

xx/xx/xxxx abdjfe step 1

xx/xx/xxxx abdjfe step 2

xx/xx/xxxx apapap step 3

xx/xx/xxxx abcdef step 3

xx/xx/xxxx abcdef step 4

xx/xx/xxxx abdjfe step 5

The idea is that each user id will have different events. So the first user id could have step 1 as their first event, then they will be taken to step 4 within the program. So I've tried to distinct count user id based on two filters, but the results are coming up blank.

Here is the dax expression I've used:

eventflow1 = calculate(distinctcount( tableA[userid]), filter(tableA, tableA[event] = "step 1" && tableA[event] = "step 2" && tableA[event] = "step 4"))

But that dax expression is giving me a blank. If I switch the "&&" to "||" I get a higher number than I'm supposed to have. Essentially trying to build a funnel with how many steps a user completes. Thank you for the help!

Re: Counting values in one column based on string filters from another column

Migasuke — Fri, 13 Aug 2021 22:13:28 GMT

Hi Anonymous ,

Please could you tell me, what result would you expect in case of user ID "abcdef" and "abdjfe"?
Just to be sure, we are on the same page.

Re: Counting values in one column based on string filters from another column

Anonymous — Fri, 13 Aug 2021 23:19:40 GMT

Hi Migasuke

The result should look something like this (essentially with a Funnel Charts):

Funnel Chart 1

Step 1 2

Step 2 2

Step 3 2

Step 4 1

Each user id can complete different steps so based on their previous events. So For example, if one user completes Step 1, there will be two options to choose from on Step 2. Based on their selection, it will take them to either Step 3 or Step 4. But eventually, everyone gets to Step 5 so I want to see which route is popular.

Re: Counting values in one column based on string filters from another column

Migasuke — Sat, 14 Aug 2021 08:03:04 GMT

Hi Anonymous ,

In that case I think this is the measure you are looking for:

Let me know, if that helps!