cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lalanand
Frequent Visitor

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

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!

3 REPLIES 3
Migasuke
Solution Sage
Solution Sage

Hi @lalanand ,

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.



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

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. 

Hi @lalanand ,

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

Migasuke_0-1628928166570.png

Let me know, if that helps!



If my answer was helpful please give me a Kudos or even accept as a Solution.

Let's connect on LinkedIn!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors