Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
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
@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])
Getting error when i am trying to replace the columns as per my data , below is the error
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |