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 co...
Need Help
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. 

      • vanessafvg's avatar
        vanessafvg
        Community Champion
        3 years ago

        what if you created a variable using this?

         

        var currdte = CALCULATE ( 'table'(date] ), ALLSELECTED( 'table'[date] ))

    • v-yinliw-msft's avatar
      v-yinliw-msft
      Community Support
      3 years ago

      Hi  , 

      If I understand your idea correctly, you want to use dynamic variables to express the end date?

       

      If so, the problem is from this code:

      var ed = selectedvalue ('TABLE'[YYYYWW] )

      This code can run in the calculation but DAX do not suppose to use a dynamic variables to create a table.

       

      So if you want to preserve the filter, you can add the filter in the Power BI Desktop.

       

      Hope this helps you.

       

      Best Regards,

      Community Support Team _Yinliw

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.