The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello people, I'ld like to improve my DAX level by using more variables within a formula.
I'm working with Contoso 2 dataset.
I want to calculate a sales amount with filter applied : with 2 DAX, I get the correct answer (357,73M).
Sales Amount =
SUMX(
'Online Sales',
'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount]
)
Calculate 1 =
CALCULATE(
[Sales Amount],
FILTER(
'Online Sales',
'Online Sales'[Unit Price] > 100 &&
'Online Sales'[Discount Amount] > 10
&& RELATED(Products[Brand Name]) = "Contoso"
)
)
I wanted to use one DAX expression instead, however, the filter does not apply:
xx_Calculate_1 =
VAR total_sales = SUMX(
'Online Sales',
'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount]
)
RETURN
CALCULATE(
total_sales,
FILTER(
'Online Sales',
'Online Sales'[Unit Price] > 100 &&
'Online Sales'[Discount Amount] > 10 &&
RELATED(Products[Brand Name]) = "Contoso"
)
)
Why my xx_Calculate_1 does not apply the filters ?
I try to understand and change the formula but the result is always the same...
Do you have any suggestions to help me understand and to create a DAX expression ?
Thank you.
Solved! Go to Solution.
Variables are not measures!
Your variable is a scalar value, so applying a filter does exactly nothing. If you want to combine the two, try:
CALCULATE(SUMX('Online Sales',
'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount])
,'Online Sales'[Unit Price] > 100
,'Online Sales'[Discount Amount] > 10
,Products[Brand Name] = "Contoso"
)
Thank you so much for your reply and appreciate the reminder : variables are not measures !
Variables are not measures!
Your variable is a scalar value, so applying a filter does exactly nothing. If you want to combine the two, try:
CALCULATE(SUMX('Online Sales',
'Online Sales'[Sales Quantity] * 'Online Sales'[Unit Price] - 'Online Sales'[Discount Amount])
,'Online Sales'[Unit Price] > 100
,'Online Sales'[Discount Amount] > 10
,Products[Brand Name] = "Contoso"
)
User | Count |
---|---|
26 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
30 | |
15 | |
12 | |
12 | |
7 |