Using result of one measure as parametr in another filter.

[vtalasek]

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

[Anonymous · Accepted 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