Forum Discussion

ddorhout's avatar
ddorhout
Frequent Visitor
4 years ago
Solved

Measure calculation with filter

Hello all,

 

I am having issues with some calculation metrics. I would like to calulate the amount of (distinct) drivers, and if a license lets them to stay within their own city. Various events (drives) are listed, and a user can have multiple events. But once a user went out of their city, then the user should be counted as "no" in column within the "own city". So the "yes" should not be taken into account in the calculation.

 

It should look like this, based on the dataset below:

  has licenseno license
within cityYes11
 No21

 

Example dataset here:

DriverHas License?Own city
x0Yes
x0No
y0Yes
z1No
a1Yes
a1No
b1Yes
b1Yes

 

 

Need Help
Show and Tell
Tips and Tricks
  • d_gosbell's avatar
    d_gosbell
    4 years ago

    So you should be able to use a measure like the following to do this

     

    Measure = 
// loop over all the drivers in the current context
SUMX( VALUES('Table'[Driver]) , 
// get the current value from the [Own City], if we are not filtering by [Own City] this will be blank
var ownCity = SELECTEDVALUE('Table'[Own city])
// if [Own City] is blank or "No" we should count this as 1 driver 
return if (isblank(ownCity ) || ownCity = "No", 1, 
    // if [Own City] <> "No" then we check if there are any other no values 
    // for this driver and if there are any we return blank
    if( CALCULATE(countrows('Table'),'Table'[Own city] = "No") > 0, blank(),1)
))

1 Reply

  • d_gosbell's avatar
    d_gosbell
    Super User
    4 years ago

    So you should be able to use a measure like the following to do this

     

    Measure = 
// loop over all the drivers in the current context
SUMX( VALUES('Table'[Driver]) , 
// get the current value from the [Own City], if we are not filtering by [Own City] this will be blank
var ownCity = SELECTEDVALUE('Table'[Own city])
// if [Own City] is blank or "No" we should count this as 1 driver 
return if (isblank(ownCity ) || ownCity = "No", 1, 
    // if [Own City] <> "No" then we check if there are any other no values 
    // for this driver and if there are any we return blank
    if( CALCULATE(countrows('Table'),'Table'[Own city] = "No") > 0, blank(),1)
))