SUMX; CALCULATE / DISTINCT and FILTER values on a calculated measure

[MauriceAW]

Hey Colleagues

I have this measure that I'm trying to make more dynamic in my dashboard

My existing measure refers to a fixed week number (ie Week 27):

Location (Current week) = SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Quantity]), 'Production Planning'[Week] = 27))

What i wanted to do was use an existing Measure inside this new measure.
Existing Measure:  Current week = WEEKNUM(TODAY())
This is created in Calendar Table (but isn't connected to me existing model) - should it be?

Adjusted new Measure:
Location (Current week) = SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Quantity]), 'Production Planning'[Week] = [Current week]))

However, this is not working.

How can I achieve this?

[Greg_Deckler]

@MauriceAW I'm unclear as to what you are summing here. DISTINCT brings back a column of values ( a single column table ). So I don't see how you can sum that unless it is perhaps bringing back only a single row and magically converting it to a scalar. In any case, I would probably go with something like this:

Measure = 
  VAR __Table = SUMMARIZE( FILTER( 'Production Planning', [Week] = [Current Week] ), "__Qty", [Quantity] )
  VAR __Result = SUMX( __Table, [__Qty] )
RETURN
  __Result

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[MauriceAW]

Hey Greg

Apologies for the delay (had some priorities on other tasks); and also for the very basic question.

Please see the sample Production data below.

Table Name: Production Planning 

Product #	Order Qty	Week
4697.5	17,000	28
4691.7	15,000	28
4691.7	25,000	28
4691.7	30,000	28
4691.5	20,000	28
4447.5	50,000	28
4446.5	37,500	29
4446.5	50,000	28

Measures created:

Week 28* = SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Order Qty]), 'Production Planning'[Week] = 28))

Week 29* = SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Order Qty]), 'Production Planning'[Week] = 29))
Week 30* = SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Order Qty]), 'Production Planning'[Week] = 30)) 

Current week = WEEKNUM(TODAY())

1 week = WEEKNUM(TODAY())+1

2 weeks = WEEKNUM(TODAY())+2

For the below visual (Table); I have used the Measures: Week 28*; Week 29* and Week 30* 

Result (Visual): Production Week Planning 

Product #	Week 28*	Week 29*	Week 30*
4697.5	17,000
4691.7	70,000
4691.5	20,000
4447.5	50,000
4446.5	50,000	75,000

The current caulations are working (as expected). The SUMX formula successfully cumulatates the order qty's for each product, for a particular week.

However, the formula is hardcoded to a specific week (refer to the red highlighted number in each measure formula). What I would rather have, is that the formula is referring to a dynamic measure = Current Week.

Therefore as we pass each week, i don't have the change the formulas constantly.

So the adjusted measure formula was:
Week 28 (Dynamic) =SUMX('Production Planning',CALCULATE(DISTINCT('Production Planning'[Order Qty]), 'Production Planning'[Week] = [Current week]))

If I place this Measure in the same Production Planning Table (model); then the error show as:
"A function PLACEHOLDER has been used in a True/False expression that is used as table filter expression. This not allowed".
If I place this Measure in the Calendar Table (model) - which has a 'Many to Many' relationship; then the Measure breaks the visual, and with the error as:
"Couldn't load the data for this visual"

Please note: My current Calendar is built like the following (this can be changed, if required)

Calendar =

 ADDCOLUMNS(

 FILTER(CALENDARAUTO(), "Week", WEEKNUM([Date],21)

Hopefully this provides enough information to assist in my question.

[Anonymous · Accepted solution]

Hi,@MauriceAW I am glad to help you.
Hello,@Greg_Deckler ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Did you get your problem solved, if not, you can refer to my suggestion below
You can try to get the current weekly number by using the var variable
like this:

WeekDynamic = 
VAR CurrentWeek = WEEKNUM(TODAY())
RETURN
SUMX(
    'Production Planning',
    CALCULATE(
        DISTINCT('Production Planning'[Order Qty]),
        'Production Planning'[Week] = CurrentWeek
    )
)

Your use of variables to store the current latest weekly number avoids the use of dynamic metric values in the filter context.
Because the value of the variable is determined at the beginning of the calculation, it does not cause errors when used in the context of a filter.
You can try to optimize the relationships between the tables and try to avoid unnecessary many-to-many relationships, as that may affect the calculation of your measure.
According to your description, your code as a whole is fine, I hope my suggestions can help you!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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