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

 

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]

 

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

Hello Zubair,

 

Thank you so much for putting everything together. This is so nice of you to help and create an example for me. I really appreciate your effort. This indeed solves my problem and it works great!

 

This is more out of curiosity... I have noticed that you cannot use SELECTEDVALUE in PowerPivot. It's a new function for PowerBI and I was wondering if there's something similar that might work as a replacement. I do no actually need it at this point.

 

Best,

C