DAX table creation with variable does not work

[Anonymous]

Hi,

I am creating a table with DAX using multiple measures that need to be calculated over time and for several locations. Since measures are not suited to be visualized in tables and matrices, the calculations are pre-processed in a table.

The concept is working, although the variable LocationA is not correctly applied while the variable LocationB works fine. Results for me are expected to be exactly the same.

Anybody have an idea why LocationA is not correctly applies?

----------------------

Sales =

VAR LocationA = FILTER('Sales','Sales'[Location]=1)

VAR LocationB = 1

RETURN

UNION(

//This part works

SELECTCOLUMNS('DM_Algemeen DimDate',"Date",'DM_Algemeen DimDate'[Date],"Fact",CALCULATE(SUMX(FILTER('Sales','Sales','Sales'[Location]=LocationB),[Measure_sales])),

"Location","LocationB"),

//This part does not work

SELECTCOLUMNS('DM_Algemeen DimDate',"Date",'DM_Algemeen DimDate'[Date],"Fact",CALCULATE(SUMX(LocationA,[Measure_sales])),"Location","LocationA"))
------------------------------------

Kind regards,

Rene Rooijackers

[Icey]

Hi @Anonymous ,

In your expression, 

LocationA = FILTER('Sales','Sales'[Location]=1)

LocationA is a table, not a single value or a column.

Best Regards,

Icey

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

[Anonymous]

Hi Icey,

Thanks for your responce.

What I would like to accomplish is to use variables to generate tables over multiple dimensions. 

For example, in case I would like to add the combination Location and store later on, that I would only need to adjust the variable and not all individual calculations in the DAX code. Does not have to be with the use of a variable specifically...

You have any idea's?

Kind regards,

ReneRooijackers

[Icey]

Hi @Anonymous ,

If you don't mind, please share me an example PBIX file without sensitive information.

Best Regards,

Icey

[Anonymous · Accepted solution]

Hi @Icey ,

Figured it out. The table creation below works now.

Thanks for thinking with me.

-------------------

Sales =

VAR LocationA = "Netherlands"

VAR LocationB = "Germany"

RETURN

UNION(

SELECTCOLUMNS('DM_Algemeen DimDate',"Date",'DM_Algemeen DimDate'[Date],"Fact",CALCULATE([Measure_sales], Location_table[Location]=LocationA),"Location","LocationA"),

SELECTCOLUMNS('DM_Algemeen DimDate',"Date",'DM_Algemeen DimDate'[Date],"Fact",CALCULATE([Measure_sales], Location_table[Location]=LocationB),"Location","LocationB"))
--------------------