The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
---|---|
25 | |
10 | |
8 | |
7 | |
6 |
User | Count |
---|---|
32 | |
12 | |
10 | |
10 | |
9 |