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.
Hello guys,
I'd like to ask for a help with some DAX formulas I have been struggling with for a few days.
Situation: My users are going through a process containing 6 stages they have to go throught and I know timestamp of each of them. Data are represented [userID],[stage],[timestamp]. (e.g. workflow, registration process, ...)
I have successfuly managed to see running count to see how many users came throught given stage in each month - Matrix table Month(date) x Stage with this DAX formula
YTD_max = CALCULATE([CountUsers],FILTER(ALLSELECTED(Sheet1), AND(Sheet1[date]<=max(Sheet1[date]),Sheet1[s]=MAX(Sheet1[s])) ))
which allows me to filter which months and stages are in the center of my attention.
Goal: And now I want to also have another filter by which I would tell to that formula above: "And take into consideration only users whos timestamp of stage X (=filter on stages) is during months Y (=filter on date).
This means that I need to select only some users and then apply YTD_max on them.
Where I am stucked is that I need to operate with users and not whole records.
My approach #1:
1) calculate true/false for each user (in new table Users) if fulfil conditions (date of stage X is in months Y)
2) add new FILTER in YTD_MAX to related value in Users
I have also formula for that but it works only as measure, not column.
useUser = IF((CALCULATE(COUNTROWS(Sheet1),RELATEDTABLE(Sheet1),
DATESBETWEEN(Sheet1[date],[input_min],[input_max]),
FILTER(ALL(stages),stages[stageNo]=MIN(stages[stageNo]))))>0,
"yes","no")
and I have a feeling that calculated column even can't work that way so I have abandoned this approach.
My approach #2:
Create Calculated table with column [user],[useUser] right in YTD_max and then somehow compare the users in both.
But I have no idea how to use useUser value as parametr in FILTER and because there has to be some AGGRx function I am not really sure this is possible way.
In SQL I would use subquery for that:
select count(user), (..) From (..) where user in( select user from where date=X and stage=Y )
but only ways how to use "IN" in Power BI I found were using constant values.
I hope this description of my problem is explantory enough 🙂
I would appreciate every help.
thx,
vojta
Solved! Go to Solution.
Hi @vtalasek,
>> Goal: And now I want to also have another filter by which I would tell to that formula above: "And take into consideration only users whos timestamp of stage X (=filter on stages) is during months Y (=filter on date).
According to your description, you want to use slicer to choose which record that measure calculated, right?
If as I said, you can follow below steps to use slicer to choose records.
Data tables.
Sheet1:
Stages:
Datetable:
Add measure to get the select item.
selecteddate = if(HASONEVALUE('Table'[Date]),VALUES('Table'[Date]),BLANK())
selectedstage = if(HASONEVALUE('stages'[stageNo]),VALUES(stages[stageNo]),BLANK())
Modify your measure formula.
Measure = var currDate= MAX(Sheet1[Date]) var currStage=Max(Sheet1[stage]) return if(AND(currStage=[selectedstage],currDate=[selecteddate]), CALCULATE(COUNT(Sheet1[userID]),FILTER(ALLSELECTED(Sheet1), AND(Sheet1[date]<=max(Sheet1[date]),Sheet1[stage]=MAX(Sheet1[stage])))),0)
Create visuals.
Slicers:
Table visual:
Result:
Notice:
1. The measure works when you choose one item, if you select multiple items, it will show 0.
2. You should remove these tables’ relationship or it will get error “selecteddate”, “selectedstage”.
Regards,
Xiaoxin Sheng
Hi @vtalasek,
>> Goal: And now I want to also have another filter by which I would tell to that formula above: "And take into consideration only users whos timestamp of stage X (=filter on stages) is during months Y (=filter on date).
According to your description, you want to use slicer to choose which record that measure calculated, right?
If as I said, you can follow below steps to use slicer to choose records.
Data tables.
Sheet1:
Stages:
Datetable:
Add measure to get the select item.
selecteddate = if(HASONEVALUE('Table'[Date]),VALUES('Table'[Date]),BLANK())
selectedstage = if(HASONEVALUE('stages'[stageNo]),VALUES(stages[stageNo]),BLANK())
Modify your measure formula.
Measure = var currDate= MAX(Sheet1[Date]) var currStage=Max(Sheet1[stage]) return if(AND(currStage=[selectedstage],currDate=[selecteddate]), CALCULATE(COUNT(Sheet1[userID]),FILTER(ALLSELECTED(Sheet1), AND(Sheet1[date]<=max(Sheet1[date]),Sheet1[stage]=MAX(Sheet1[stage])))),0)
Create visuals.
Slicers:
Table visual:
Result:
Notice:
1. The measure works when you choose one item, if you select multiple items, it will show 0.
2. You should remove these tables’ relationship or it will get error “selecteddate”, “selectedstage”.
Regards,
Xiaoxin Sheng
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |