topic Re: How do we call variables in DAX measure in Developer

How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 05:10:00 GMT

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 )
)

Re: How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 08:29:55 GMT

anyone can help to proceed further...

Re: How do we call variables in DAX measure

NandanHegde — Thu, 18 Mar 2021 10:29:21 GMT

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.

Re: How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 09:38:01 GMT

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 )
)

Re: How do we call variables in DAX measure

NandanHegde — Thu, 18 Mar 2021 10:32:30 GMT

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

Re: How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 11:11:47 GMT

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.??

Re: How do we call variables in DAX measure

NandanHegde — Thu, 18 Mar 2021 11:25:28 GMT

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 🙂

Re: How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 12:17:12 GMT

pls. find the sample image for your reference.

Re: How do we call variables in DAX measure

saivina2920 — Thu, 18 Mar 2021 16:11:43 GMT

pls. let us know if need any more details.

Re: How do we call variables in DAX measure

saivina2920 — Fri, 19 Mar 2021 02:06:10 GMT

anyone can update pls.

Re: How do we call variables in DAX measure

v-lionel-msft — Mon, 22 Mar 2021 03:11:28 GMT

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.

Re: How do we call variables in DAX measure

saivina2920 — Tue, 23 Mar 2021 09:32:44 GMT

Thanks again for your great help.

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

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

Re: How do we call variables in DAX measure

v-lionel-msft — Tue, 23 Mar 2021 09:41:53 GMT

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.

Re: How do we call variables in DAX measure

NandanHegde — Tue, 23 Mar 2021 09:59:32 GMT

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 ()

Re: How do we call variables in DAX measure

saivina2920 — Tue, 23 Mar 2021 17:19:38 GMT

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.

Re: How do we call variables in DAX measure

NandanHegde — Wed, 24 Mar 2021 06:07:43 GMT

You can leverage the NOT operator

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

Re: How do we call variables in DAX measure

saivina2920 — Wed, 24 Mar 2021 06:03:01 GMT

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....?

Re: How do we call variables in DAX measure

NandanHegde — Wed, 24 Mar 2021 06:07:07 GMT

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}

Re: How do we call variables in DAX measure

saivina2920 — Wed, 24 Mar 2021 06:17:36 GMT

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