Forum Discussion

LUCASM's avatar
LUCASM
Helper IV
3 years ago

CALCULATE TABLE with Filter

I am tring to create a dynamic table from my source table based on a date slicer

The date slicer [Period] looks like this "Week 38 2022" - pretty useless in itself

I do also have another numeric column called [YYYYWW] which in this case would be 202238 which I am using in my DAX

 

So I tried this first to see what happens:

 

13WeekFunctionTable = 
var ed = 202239
var sd = ed-12

RETURN
CALCULATETABLE (
    SUMMARIZE('TABLE',[YYYYWW],
    [Period],
    [Function Group],
    "Sales Units", SUM('TABLE'[Sales Units]),
    "Sales Values", SUM('TABLE'[Sales Values EUR])
    ),
    FILTER (
        TABLE,
        TABLE[YYYYWW] >= sd
            && TABLE[YYYYWW] <= ed
    )
)

 

Great it works fine!

 

How about if i use the SELECTEDVALUE as the end point ?

 

13WeekFunctionTable = 
var ed = selectedvalue ('TABLE'[YYYYWW] )
var sd = ed-12

RETURN
CALCULATETABLE (
    SUMMARIZE('TABLE',[YYYYWW],
    [Period],
    [Function Group],
    "Sales Units", SUM('TABLE'[Sales Units]),
    "Sales Values", SUM('TABLE'[Sales Values EUR])
    ),
    FILTER (
        TABLE,
        TABLE[YYYYWW] >= sd
            && TABLE[YYYYWW] <= ed
    )
)

 

Now I get an empty table!!!

Can anyone tell me what I am missing or doing wrong

Need Help

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    3 years ago

    can you return the selected value and see what you are getting?  you will need to debug your code.   Maybe your selected value is not returning what you expect.

     

    for your return statement return ed and see what you get.

    • LUCASM's avatar
      LUCASM
      Helper IV
      3 years ago

      Hi vanessafvg 

      as this DAX is creating a table I cannot return ed
      However I am using ed in a calculation which does work as expected

       

      ASP_Last_Week = 
var ed = selectedvalue ( TABLE1[YYYYWW] )

var ed_sales = CALCULATE ( 
     sum ( TABLE1[Sales Values] ) ,
     REMOVEFILTERS ( TABLE1[Period] ) , 
     TABLE1[YYYYWW] = ed )

var ed_volume = CALCULATE(
     sum ( TABLE1[Sales Units]) ,
     REMOVEFILTERS ( TABLE1[Period]) , 
     TABLE1[YYYYWW] = ed )

RETURN 
     DIVIDE( ed_sale, ed_volume )

       

      If I return ed here I get the expected enddate - In this case 202238.

      Since asking my question, one of my colleagues - whom I highly respect, has suggested that you cannot use SELECTEDVALUES in a CALCULATETABLE statement as CALCULATETABLE removes/ignores any filters that are in place.

      I guess in my case, the Slicer is a filter.

       

      Can anyone comment on this.

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        3 years ago

        sorry it was late and i missed that, he is 100% as you will not have row context which you need for selectedvalue.