Forum Discussion

IoanCosmin's avatar
IoanCosmin
Icon for Helper III rankHelper III
8 years ago
Solved

Calculate where type clause from two different tables

I haven't written DAX in a while and I'm having a bit of a hard time putting this together and I am hoping someone could throw in a suggestion. What I have: Qty Table (a lot more months than Januar...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi IoanCosmin

     

    Please see the attached file here. Hope it helps

     

    Here are the steps

     

    First I added a Caclulated Column in QtyTable

     

    Month =
MONTH ( QtyTable[Date] )

    Second, a MEASURE in QtyTable to get the LaborRate for the months

     

    LaborRate =
CALCULATE (
    VALUES ( RatesTable[Amount] ),
    FILTER (
        RatesTable,
        SELECTEDVALUE ( QtyTable[Month] ) >= MONTH ( RatesTable[DateFrom] )
            && SELECTEDVALUE ( QtyTable[Month] ) <= MONTH ( RatesTable[DateTo] )
            && SELECTEDVALUE ( QtyTable[Location] ) = RatesTable[Location]
    )
)

    Thirdly a MEASURE to get the RESULT

     

    RESULTT =
SUM ( QtyTable[LaborQty] ) * [LaborRate]

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

Hi IoanCosmin

 

Yes SelectedValue is a new function. Even I do not have it in Excel 2016

 

Instead of

SELECTEDVALUE ( Table[column] )
You can also use
IF ( HASONEVALUE ( Table[column] ), VALUES ( Table[column] ) )
 

 

 

  • IoanCosmin's avatar
    IoanCosmin
    Icon for Helper III rankHelper III
    8 years ago

    Hello Zubair_Muhammad!

     

    You've been of great help. Thanks a lot!

     

    Have a great day,

    C