Dax formula result blank

[Aidadiawndao]

Hello, 

I use this CALCULATE formula to be able to show a the total ordered value for a country based the execution status but it shows blanck.

_TotalOrdered_Executed_PID_SelectedPeriod_Test = CALCULATE ( [_Sum Total Ordered Value], FILTER ( ALL ( Plant_ID_Period_Key ), Plant_ID_Period_Key[Date] = SELECTEDVALUE(Plant_ID_Period_Key[Date]) && [_Calculated_RPEXEC] = "Executed at PID Level" ) )

the formula of the _Calculated_RPEXEC] that was used is: 

_Calculated_RPEXEC =

SWITCH(TRUE(), NOT(ISBLANK(Plant_ID_Period_Key[_Sum Annual Plan Value])) && NOT(ISBLANK([_Sum Total Ordered Value])), "Executed at PID Level",

ISBLANK([_Sum Annual Plan Value]) && NOT(ISBLANK([_Sum Total Ordered Value])), "Unplanned PID", NOT(ISBLANK([_Sum Annual Plan Value])) && ISBLANK([_Sum Total Ordered Value]), "Pending Implementation", "NA").

I would like to see where the error is coming?

[v-cgao-msft · Accepted solution]

Hi @Aidadiawndao ,

Does the filter context for Plant_ID_Period_Key[Date] contain multiple values? If so, the SELECTEDVALUE ( Plant_ID_Period_Key[Date] ) part returns null, which could be a reason.

Please try:

_TotalOrdered_Executed_PID_SelectedPeriod_Test =
CALCULATE (
    [_Sum Total Ordered Value],
    FILTER (
        ALL ( Plant_ID_Period_Key ),
        Plant_ID_Period_Key[Date]
            IN ALLSELECTED ( Plant_ID_Period_Key[Date] )
                && [_Calculated_RPEXEC] = "Executed at PID Level"
    )
)

Or

_TotalOrdered_Executed_PID_SelectedPeriod_Test =
CALCULATE (
    [_Sum Total Ordered Value],
    FILTER (
        ALL ( Plant_ID_Period_Key ),
        Plant_ID_Period_Key[Date] >= MIN ( Plant_ID_Period_Key[Date] )
            && Plant_ID_Period_Key[Date] <= MAX ( Plant_ID_Period_Key[Date] )
            && [_Calculated_RPEXEC] = "Executed at PID Level"
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Aidadiawndao]

sorry, 

After checking, it gives me total of all orders without taking into account the date filters, So when I filter 1 PID, the sum is the same as if I select all PIDS

[v-cgao-msft]

Hi @Aidadiawndao ,

 In your previous code all filters from the 'Plant_ID_Period_Key' table were ignored, is this part necessary?
Please try:

_TotalOrdered_Executed_PID_SelectedPeriod_Test =
CALCULATE (
    [_Sum Total Ordered Value],
    Plant_ID_Period_Key[Date]
        IN ALLSELECTED ( Plant_ID_Period_Key[Date] )
            && [_Calculated_RPEXEC] = "Executed at PID Level"
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Aidadiawndao]

Thank you Gao; the 1st solution works

[Greg_Deckler]

@Aidadiawndao Tough to say without sample data to recreate. You might try this though:

_TotalOrdered_Executed_PID_SelectedPeriod_Test = 
  VAR __Date = MAX(Plant_ID_Period_Key[Date])
  VAR __Result = 
CALCULATE ( [_Sum Total Ordered Value], FILTER ( ALL ( Plant_ID_Period_Key ), Plant_ID_Period_Key[Date] = __Date && [_Calculated_RPEXEC] = "Executed at PID Level" ) )
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.

[Aidadiawndao]

Thanks Greg, 

I still can't see any value coming