Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
Proud to be a Super User! |
|
Thank you very much
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.
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
Proud to be a Super User! |
|
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
19 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
25 | |
10 | |
10 | |
9 | |
6 |