Forum Discussion

jct999's avatar
jct999
Advocate II
6 years ago

DAX :

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 : Measur...
lbendlin's avatar
lbendlin
Super User
6 years ago

Measure 2 = 

var L=selectedvalue(table[Level])

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

jct999's avatar
jct999
Advocate II
6 years ago

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

 

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    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] )
)

     

     

    • lbendlin's avatar
      lbendlin
      Super User
      6 years ago

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

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago

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