Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
3 years ago
Solved

DAX Query

I have the DAX below to create a measure that

 

- find those calls where the Planned Duration  = 15 mins and the Delivered Duration is between 0 and 17 mins, and then total the Test Cost and multiply by Delivered Duration Decimal - can you see anything wrong with below?  Its doing the first part just not multiplying the answer by the Delivered Duration Decimal

 

 

 

"DAX - 

Test New = CALCULATE(SUM('Procurement_Main_Data'[Test Cost]),'Procurement_Main_Data'[Planned Duration] = TIMEVALUE ("00:15") &&'Procurement_Main_Data'[Delivered Duration]>= TIMEVALUE("00:00")&& 'Procurement_Main_Data'[Planned Duration]<= TIMEVALUE("00:17")*('Procurement_Main_Data'[Delivered Duration Decimal]))   "
Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    spandy34 

    Let me assume that you are working at row level of source data table. In this case please try

    Test New =
    SUMX (
    FILTER (
    'Procurement_Main_Data',
    'Procurement_Main_Data'[Planned Duration] = TIMEVALUE ( "00:15" )
    && 'Procurement_Main_Data'[Delivered Duration] >= TIMEVALUE ( "00:00" )
    && 'Procurement_Main_Data'[Planned Duration] <= TIMEVALUE ( "00:17" )
    ),
    'Procurement_Main_Data'[Test Cost] * 'Procurement_Main_Data'[Delivered Duration Decimal]
    )

12 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    spandy34 

    Let me assume that you are working at row level of source data table. In this case please try

    Test New =
    SUMX (
    FILTER (
    'Procurement_Main_Data',
    'Procurement_Main_Data'[Planned Duration] = TIMEVALUE ( "00:15" )
    && 'Procurement_Main_Data'[Delivered Duration] >= TIMEVALUE ( "00:00" )
    && 'Procurement_Main_Data'[Planned Duration] <= TIMEVALUE ( "00:17" )
    ),
    'Procurement_Main_Data'[Test Cost] * 'Procurement_Main_Data'[Delivered Duration Decimal]
    )

    • spandy34's avatar
      spandy34
      Responsive Resident
      3 years ago

      Thats worked !! Thank you so much your a star.

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    Hi spandy34 

    please try

    Test New =
    CALCULATE (
    SUM ( 'Procurement_Main_Data'[Test Cost] ),
    'Procurement_Main_Data'[Planned Duration] = TIMEVALUE ( "00:15" )
    && 'Procurement_Main_Data'[Delivered Duration] >= TIMEVALUE ( "00:00" )
    && VALUE ( 'Procurement_Main_Data'[Planned Duration] )
    <= VALUE ( TIMEVALUE ( "00:17" ) ) * ( 'Procurement_Main_Data'[Delivered Duration Decimal] )
    )

    • spandy34's avatar
      spandy34
      Responsive Resident
      3 years ago

      Thank you tamerj1 for responding.

       

      I have tried the DAX and I am getting 0.28 and I should be getting 0.56 - see below

       

       

      • tamerj1's avatar
        tamerj1
        Community Champion
        3 years ago

        spandy34 

        If you place the measure in the table, does it give the same value?

  • spandy34's avatar
    spandy34
    Responsive Resident
    3 years ago

    Ive took a braket out and got rid of the error but the Test New still doesnt say 0.56