If statement between 2 tables - reproducing sql

[jaryszek]

Hello,

my sql statement is: 

-- This query calculates the applied discount rate for SKUs based on their retail prices and effective prices.
WITH OnDemandPrices AS (
    SELECT DISTINCT PartNumber, PayGPrice
    FROM vnomic_Daily.Fct_EA_AmortizedCosts
    WHERE PricingModel = 'OnDemand'
),
RetailPricing AS (
    SELECT
        a.PricingModel,
        a.BenefitName,
        a.ReservationName,
        a.PayGPrice,
        a.MeterCategory,
        CASE
            WHEN a.PricingModel = 'Reservation' THEN MAX(od.PayGPrice)
            ELSE MAX(a.PayGPrice)
        END as RetailPrice,
        MAX(a.EffectivePrice) as EffectivePrice,
        SUM(a.CostInBillingCurrency) AS SumCostInBillingCurrency
    FROM vnomic_Daily.Fct_EA_AmortizedCosts a
    LEFT JOIN OnDemandPrices od ON a.PartNumber = od.PartNumber
    WHERE a.Date >= '2025-04-01'
    AND a.Date < '2025-05-01'
    GROUP BY
        a.PricingModel,
        a.BenefitName,
        a.ReservationName,
        a.MeterCategory,
        a.PayGPrice,
)

So I have the issue with setting DetailPrice variable using DAX. 
What i would like to do after is to add this detailprice to matrix. 

So we have 2 tables here so somehow i need to join tables before applying dax. 

What can be your approach ? How you would resolve it? 

Best,
Jacek

[jaryszek]

Thank you very much

[v-venuppu]

Hi @jaryszek ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @bhanu_gautam for the prompt response.

I have replicated the scenario by using sample data.Please go through the attached PBIX file for your reference.

Thank you.

[bhanu_gautam · Accepted solution]

@jaryszek 

Create a new calculated table that joins OnDemandPrices and RetailPricing:

dax
CombinedTable =
VAR OnDemandPrices =
DISTINCT (
SELECTCOLUMNS (
vnomic_Daily[Fct_EA_AmortizedCosts],
"PartNumber", vnomic_Daily[Fct_EA_AmortizedCosts][PartNumber],
"PayGPrice", vnomic_Daily[Fct_EA_AmortizedCosts][PayGPrice]
)
)
VAR RetailPricing =
ADDCOLUMNS (
SUMMARIZE (
vnomic_Daily[Fct_EA_AmortizedCosts],
vnomic_Daily[Fct_EA_AmortizedCosts][PricingModel],
vnomic_Daily[Fct_EA_AmortizedCosts][BenefitName],
vnomic_Daily[Fct_EA_AmortizedCosts][ReservationName],
vnomic_Daily[Fct_EA_AmortizedCosts][PayGPrice],
vnomic_Daily[Fct_EA_AmortizedCosts][MeterCategory]
),
"RetailPrice", IF (
vnomic_Daily[Fct_EA_AmortizedCosts][PricingModel] = "Reservation",
CALCULATE ( MAX ( OnDemandPrices[PayGPrice] ) ),
CALCULATE ( MAX ( vnomic_Daily[Fct_EA_AmortizedCosts][PayGPrice] ) )
),
"EffectivePrice", CALCULATE ( MAX ( vnomic_Daily[Fct_EA_AmortizedCosts][EffectivePrice] ) ),
"SumCostInBillingCurrency", CALCULATE ( SUM ( vnomic_Daily[Fct_EA_AmortizedCosts][CostInBillingCurrency] ) )
)
RETURN
RetailPricing

Create a calculated column or measure for DetailPrice in the CombinedTable:

dax
DetailPrice =
IF (
CombinedTable[PricingModel] = "Reservation",
CombinedTable[RetailPrice],
CombinedTable[EffectivePrice]
)

Use Detailprice in matrix