topic Defining Variables in Desktop

Defining Variables

rhinorocks — Tue, 01 Mar 2022 14:37:40 GMT

Hi All,

I am new to Dax and currently facing issues in defining variables.

Can we define table columns in Variable? Like I have a Table called Customer Master and I want to define a variable storing the customer ID. But the error is coming as Var is expecting a scalar value?

Kindly help!!

Re: Defining Variables

Pragati11 — Tue, 01 Mar 2022 14:42:06 GMT

Hi rhinorocks ,

It depends on the logic and also on measure/column calculation you are trying to write using DAX.

Check this simple blog on variables:

https://www.purplefrogsystems.com/blog/2021/09/variables-in-dax/

Re: Defining Variables

Syk — Tue, 01 Mar 2022 14:43:35 GMT

You can work with columns in DAX but your measure has to return a scalar value. For example, in the dax below I'm storing a column in a variable but returning the sum of the column. If you reply with your specific use case, we may be able to help in more detail!

measure = var sales = 'sales'[sales_amount] return sum(sales)

Re: Defining Variables

rhinorocks — Tue, 01 Mar 2022 14:56:57 GMT

Consider the following problem posted on this forum -

https://community.powerbi.com/t5/Desktop/Days-between-two-dates-in-different-rows/m-p/2367394#M851834

When I am trying to write this particular part of the code -

Days between pricelists =

var _item = 'Table'[Item]

The tellisense is not able to detect this 'Table' hence I am not being able to proceed. Check the attached picture

While If I write the second line of the soluation, then I am getting no error.

var _endDate = 'Table'[ValidityEndingDate]

Why is this happening?

Re: Defining Variables

rhinorocks — Wed, 02 Mar 2022 13:43:13 GMT

I think I got the solution of this issue. I was trying to write a measure instead of the calculated column.

Re: Defining Variables

krzlag — Tue, 15 Nov 2022 16:03:02 GMT

Hi Syk, I build similar logic, but it doesn't work

Syk wrote:
You can work with columns in DAX but your measure has to return a scalar value. For example, in the dax below I'm storing a column in a variable but returning the sum of the column. If you reply with your specific use case, we may be able to help in more detail!

measure = var sales = 'sales'[sales_amount] return sum(sales)