topic Re: If statement between 2 tables - reproducing sql in DAX Commands and Tips

If statement between 2 tables - reproducing sql

jaryszek — Fri, 04 Jul 2025 07:12:16 GMT

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

Re: If statement between 2 tables - reproducing sql

bhanu_gautam — Fri, 04 Jul 2025 08:02:20 GMT

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

Re: If statement between 2 tables - reproducing sql

v-venuppu — Fri, 04 Jul 2025 10:41:37 GMT

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.

Re: If statement between 2 tables - reproducing sql

jaryszek — Fri, 04 Jul 2025 13:11:06 GMT

Thank you very much