Forum Discussion

Aidadiawndao's avatar
Aidadiawndao
Frequent Visitor
2 years ago
Solved

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 = CAL...
Need Help
  • Anonymous's avatar
    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"
    )
)

    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

Anonymous's avatar
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's avatar
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's avatar
    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