Use Variable in Query

[Syndicate_Admin]

Hello, you can use a variable within a query, for example I have this data that I must change every week within many queries, so I want to change it only in one place and it is updated dynamically in the rest of the queries.

Here's the piece of data I want to pass to a variable or save it within the registry in a table:

Weeks = 44,45,46,47,48,49,50,51,52 ----------> Every week I add another one and as it is in many queries I want to change it only in one place and update it dynamically.

This is one of many queries that I want to use a

NO VARIABLE

******************************************

Tbl_AllMarkets = SUMMARIZE (

Data,Data[SEASON],

"BOXES", CALCULATE(SUM(Data[BOXES]), Data[WEEK] in {44,45,46,47,48,49,50,51,52})

)

********************************************

WITH VARIABLE

******************************************

Tbl_AllMarkets = SUMMARIZE (

Data,Data[SEASON],

"BOXES", CALCULATE(SUM(Data[BOXES]), Data[WEEK] in {Weeks})

)

*******************************************

I tried to do this when adding a new measure but it gives me a PLACEHOLDER error

Do you understand what I want to do?

[v-weiyan1-msft · Accepted solution]

Hi @Syndicate_Admin ,

Based on your description,
You might consider creating a Calculated table.
Here is an example:
My Sample:

WeeksTable = DATATABLE(
             "WeekNum", INTEGER, 
             {{44}, {45}, {46}, {47}, {48}, {49}, {50}, {51},{52}}
             )

Use the created Calculated table in the following code, Result is as below.

Measure = CALCULATE(SUM('Table'[Sales]),
                    FILTER('Table','Table'[Week] IN VALUES(WeeksTable[WeekNum]))
                   )

Best Regards,
Yulia Yan

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