How do we call variables in DAX measure

[saivina2920]

I am using below measure and i just want to call the variables.

variables ==> need to get from slicer/dropdown or any selected controls/chart values.

How do we assign controls/chart values in variable ($$$variables), and use those variables in measures.

EMPSTATUS = CALCULATE (DISTINCTCOUNT ( 'EMP QUERY'[EMP_NUMBER] ),
( 'EMP QUERY'[EMP_REMARKS], "GOOD" ),( 'EMP QUERY'[EMP_COUNTRY], "INDIA" ),( 'EMP QUERY'[EMP_PLACE], $$$variables )
)

[v-lionel-msft]

Hi @saivina2920 ,

Please try the formula.

EMPSTATUS = 
VAR variables = SELECTEDVALUE('EMP QUERY'[EMP_PLACE])
RETURN
CALCULATE(
    DISTINCTCOUNT ( 'EMP QUERY'[EMP_NUMBER] ),
    FILTER(
        ALL('EMP QUERY'),
        'EMP QUERY'[EMP_REMARKS] = "GOOD" &&
        'EMP QUERY'[EMP_COUNTRY] = "INDIA" &&
        'EMP QUERY'[EMP_PLACE] = variables
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saivina2920]

Thanks again for your great help.

Shall i use the below condition if more than two values.

'EMP QUERY'[EMP_REMARKS] in ("GOOD","EXCELLENT")

[NandanHegde]

Hey @saivina2920 
Sorry for the delayed response as I was on some medical leave.
The above answer provided by CST is accurate but for using IN operator you should use {} and not ()

[v-lionel-msft · Accepted solution]

Hi @saivina2920 ,

Please try this.

EMPSTATUS = 
VAR variables = SELECTEDVALUE('EMP QUERY'[EMP_PLACE])
RETURN
CALCULATE(
    DISTINCTCOUNT ( 'EMP QUERY'[EMP_NUMBER] ),
    FILTER(
        ALL('EMP QUERY'),
        'EMP QUERY'[EMP_REMARKS] in { "GOOD", "EXCELLENT"} &&
        'EMP QUERY'[EMP_COUNTRY] = "INDIA" &&
        'EMP QUERY'[EMP_PLACE] = variables
    )
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[saivina2920]

Perfect. One last question.

How to exclude selected value like not in selected value in the measure.

VAR variables = SELECTEDVALUE('EMP QUERY'[EMP_PLACE])

 if this NOT IN selected value, then my problem is solved.

[NandanHegde]

You can leverage the NOT operator

NOT('EMP QUERY'[EMP_REMARKS]) IN { variables}

[saivina2920]

Thanks. But, not suppose to come for variables. not for column.

It should come like below. I am not sure whether it is correct or not. but, not in {Variables}

('EMP QUERY'[EMP_REMARKS]) NOT IN { variables} ==> Will it work....?

[NandanHegde]

Hey,
You can refer the below link:
https://community.powerbi.com/t5/Desktop/DAX-equivalent-of-SQL-s-NOT-IN/m-p/586184

This is the correct code:
NOT('EMP QUERY'[EMP_REMARKS]) IN { variables} 

and not the NOT IN {variables}

[saivina2920]

Thanks. got it. It's working and perfect.

[NandanHegde]

Conditional Formatting =
var SelectedGroupNm =SELECTEDVALUE(Budget[Budget])
var Result= IF([Cost]<= SelectedGroupNm || ISBLANK(SelectedGroupNm),0,1)
Return Result

In the above example, there is slicer called Budget and based on the one which is selected I am deriving a measure.

[saivina2920]

Sorry. i don't understand what you given.

my data available in slicer control. i am going to select only one at a time. Ex : 'EMP QUERY'[EMP_PLACE] 

what ever i am selecting the slicer option control, that should pass as a variable in my expression as mentioned above.

can you pls. give us the below mentioned expression measure.

EMPSTATUS = CALCULATE (DISTINCTCOUNT ( 'EMP QUERY'[EMP_NUMBER] ),
( 'EMP QUERY'[EMP_REMARKS], "GOOD" ),( 'EMP QUERY'[EMP_COUNTRY], "INDIA" ),( 'EMP QUERY'[EMP_PLACE], $$$variables )
)

[NandanHegde]

Hey, I have updated my answer with a simple DAX measure. Hope that would help you in utilizing the variables for selectedvalues within measures

[saivina2920]

Ok. thanks.

I tried the below DAX Measure, but, getting unexpected error.

MEASURE =
var VAR1 =IF(ISBLANK(SELECTEDVALUE('EMP QUERY'[EMP_PLACE])),CALCULATE(DISTINCT('EMP QUERY'[EMP_NO],FILTER('EMP QUERY','EMP QUERY'[EMP_REMARKS]="GOOD" && 'EMP QUERY'[EMP_COUNTRY]="INDIA" && 'EMP QUERY'[EMP_PLACE]= SELECTEDVALUE('EMP QUERY'[EMP_PLACE])))))
return CALCULATE(DISTINCT('EMP QUERY'[EMP_NO]),FILTER('EMP QUERY','EMP QUERY'[EMP_NO]=VAR1))

where condition is below (Filter) :
'EMP QUERY'[EMP_REMARKS]="GOOD" AND
'EMP QUERY'[EMP_COUNTRY]="INDIA"

passing variable :
SELECTEDVALUE('EMP QUERY'[EMP_PLACE])

DISTICT VALUE :
'EMP QUERY'[EMP_NO]

can you pls. check what is the problem in my DAX Measure.??

[NandanHegde]

Hey,
Based on your measure :
Your variable is returning rows (multiple Emp_Nos)
So QUERY'[EMP_NO]=VAR1 wont work. you can think of using IN dax or containsrow.

And it would be better if you also provide the error msg in case if any and the base data and your exepcted result. That might help in better understanding your scenario 🙂

[saivina2920]

pls. find the sample image for your reference.

[saivina2920]

anyone can update pls.

[saivina2920]

pls. let us know if need any more details.

[saivina2920]

anyone can help to proceed further...