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
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.

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.