topic DAX : in DAX Commands and Tips

DAX :

jct999 — Tue, 30 Jun 2020 16:24:45 GMT

Hi,

I have a table with 3 columns :

COUNTRY (string)
LEVEL (integer)
Quantity (integer)

I want to set up a Pivot Table with :

Line : COUNTRY
Column : LEVEL
Values : 3 measures :
- Measure1 = Sum(Qty) for selected COUNTRY and LEVEL
- Measure2 = Sum(Qty) for selected COUNTRY and next LEVEL (i.e. LEVEL + 1)
- Measure3 = Measure1 / Measure2

What is the DAX formula for Measure2 ?

Thanks

Regards

Re: DAX :

lbendlin — Tue, 30 Jun 2020 22:37:36 GMT

Measure 2 =

var L=selectedvalue(table[Level])

return calculate(sum(Table[quantity]),table[Level]=L+1)

Re: DAX :

jct999 — Wed, 01 Jul 2020 07:24:31 GMT

It works fine ! Thanks

Before asking on the forum, I tried SELECTEDVALUE but I put it in the CALCULATE expression, and it did not work.

So, The solution seems to use SELECTEDVALUE in a VAR expression...

Regards

Re: DAX :

AntrikshSharma — Wed, 01 Jul 2020 07:58:09 GMT

If you want to attain the same behaviour without variables then you will have to use explicit FILTER, for example.

Measure = CALCULATE ( [Total Sales], FILTER ( ALL ( Product[Brand] ), Product[Brand] = SELECTEDVALUE ( Product[Brand] ) ) )

Because writing aggregation functions like SUM, AVERAGE, MAX are not allowed while doing boolean filter operations, and same is for SELECTEDVALUE, hence the following won't work.

Measure = CALCULATE ( [Total Sales], 'Product'[Brand] = SELECTEDVALUE ( 'Product'[Brand] ) )

Re: DAX :

lbendlin — Wed, 01 Jul 2020 12:13:00 GMT

There's also the added complexity with FILTER (ALL()) - that may produce more results than intended in the visual context.

Re: DAX :

AntrikshSharma — Wed, 01 Jul 2020 12:37:14 GMT

Correct, in that case he can either wrap CALCULATE's filter arguments with KEEPFILTERS or use VALUES as per the requirements.