Dax formula result blank

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?

Need Help

Anonymous

2 years ago

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"
    )
)

_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

6 Replies

Greg_Deckler
Community Champion
2 years ago
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
  Frequent Visitor
  2 years ago
  Thanks Greg,
  I still can't see any value coming
Anonymous
Not applicable
2 years ago
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
  Frequent Visitor
  2 years ago
  Thank you Gao; the 1st solution works
- Aidadiawndao
  Frequent Visitor
  2 years ago
  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
  - Anonymous
    Not applicable
    2 years ago
    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

Forum Discussion

Dax formula result blank

6 Replies

Related content

M Formula - Show 0 instead of (blank)

If formula

DAX Formula Help

formula

Ignore Blanks in Measure Result