Distinct count of users as per date filter and the sequence of events

[balu810]

i have data of people doing few steps like below

User ID	Session	Step	Date
101	1	1	01-04-2022
101	2	2	03-04-2022
101	2	3	03-04-2022
101	3	4	04-04-2022
102	1	1	05-04-2022
102	2	2	05-04-2022
102	3	3	06-05-2022

i am trying to create a funnel to count distinct users at each step and i am using date slider to slice date at week level 

when i filter date the table has to get filtered as per my slicer selection and the users who has done the first step in the time range selected should be counted during entire funnel . but when i am using a measure of count (users) it is counting users though the user doesnt have dont step 1 in the same week .

Example :

current out put : date range selected 04-04-2022 to 06-04-2022

in the below output step 3 should be 1 insead of 2 as only user 2 has intiated step 1 in the selected date range 

		04-06-2002 to 06-05-2022
	Count
step1	1
step2	1
step3	2
step4	1

Expected output :

		04-06-2002 to 06-05-2022
	Count
step1	1
step2	1
step3	1
step4	1

johnt75 

@v-easonf-msft

 

 @amitchandak 

@johnt75 

@bcdobbs 

@parry2k 

@MFelix 

@Ashish_Mathur 

@JihaiXia 

@Jihwan_Kim 

@dm-p 

@KerKol 

@VahidDM 

@v-syr 

@v-sree 

@v-saia 

@Anonymous 

@PC2790 

@v-easonf-msft 

@v-eachen-msft 

@v-xiaotang 

@V-lianl-msft 

[v-jingzhang]

Hi @balu810 

You can add a Date table into the model and keep it disconnected from the original fact table. Use Date table's date column in the slicer. Then create the following measure to calculate the distinct count of users for each step. 

Measure = CALCULATE(DISTINCTCOUNT('Table'[User ID]),'Table'[Step]=SELECTEDVALUE('Table'[Step]),'Table'[Date]>=MIN('Date'[Date]),'Table'[Date]<=MAX('Date'[Date]))

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[balu810]

only users who has the intiated step 1 during the date range selection should be considered in  caluclation.

as per above test data user 101 did step 4 on 4th April 2022 and his latest step 1 is not in the date range of filter selection which means he didnot have step 1 intiated in the date range selected hence for step 4 it should be 0 or null

[amitchandak]

@balu810 , Try a measure like

new measure =
var _1 calculate(distinctCOUNT(Table[Step]),filter(allselected(Table), Table[User ID] = max([User ID]) && Table[Step] <= Max(Table[Step])))
return
countx(filter(Values(Table[User ID]), _1 = max(Table[Step])), Table[Step])

[balu810]

Getting error when i am trying to replace the columns as per my data , below is the error