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 show all the input and the defect performed and also have a date selection using also slicer.

To display the data i have a matrix table were the rows composed of defect list and the column is date and month, values are qty per defect, rate.  the rate formula is qty/total input per process. 

 

 

DAX Measure with error.

 CALCULATE(SUM(DS[Touched]), ALLSELECTED(DS[defectlist]), ALLSELECTED(DS[createddatetime]), DS[process]=[Selected Process])

 

can anyone help me with the correct DAX measure 

 

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.

3 Replies

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