Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

DAX Formula to get the input per process based on filter by date

I have a requirements to get the total input per process per month but it seems i'm getting an error my code and not giving me the correct value. I have a slicer to select a process that i need to sh...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi Anonymous - you can modify the measure to correctly calculate the total input for the selected process and take slicers into account

    TotalInputPerProcess =
    CALCULATE(
    SUM(DRY_DS[Touched]),
    ALLSELECTED(DRY_DS[defectlist]), -- Keep defect list slicer
    ALLSELECTED(DRY_DS[createddatetime]), -- Keep date slicer
    DRY_DS[process] = [Selected Process] -- Filter for the selected process
    )

     

    For your defect rate calculation (Rate = QTY / TotalInputPerProcess), make sure you use the newly created TotalInputPerProcess measure

    DefectRate =
    DIVIDE(
    SUM(DRY_DS[QTY]),
    [TotalInputPerProcess],
    0 -- Handles division by zero cases
    )

    hope it works at your end, let me know if any adjustments.

rajendraongole1's avatar
rajendraongole1
Icon for Super User rankSuper User
1 year ago

Hi Anonymous - you can modify the measure to correctly calculate the total input for the selected process and take slicers into account

TotalInputPerProcess =
CALCULATE(
SUM(DRY_DS[Touched]),
ALLSELECTED(DRY_DS[defectlist]), -- Keep defect list slicer
ALLSELECTED(DRY_DS[createddatetime]), -- Keep date slicer
DRY_DS[process] = [Selected Process] -- Filter for the selected process
)

 

For your defect rate calculation (Rate = QTY / TotalInputPerProcess), make sure you use the newly created TotalInputPerProcess measure

DefectRate =
DIVIDE(
SUM(DRY_DS[QTY]),
[TotalInputPerProcess],
0 -- Handles division by zero cases
)

hope it works at your end, let me know if any adjustments.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks for the reply. This is the error i'm getting from the code.

    A function 'PLACEHOLDER' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Just made some changes on the code, the grand total is correct but still not getting the correct values per defectlist if i used to get the rate. the values of test2 should be all 50,349.
     

    TotalInputPerProcess =
    CALCULATE(
    SUM(DS[Touched]),
    ALLSELECTED(DS[defectlist]), -- Keep defect list slicer
    ALLSELECTED(DS[createddatetime]), -- Keep date slicer
    filter(DS, DS[process] = [Selected Process]) -- Filter for the selected process
    )

     

    -- this is working if the process value is hardcoded

    CALCULATE(SUM(DS[Touched]), ALL(DS[defectlist]),DS[process]="SAMPLE")