Measure only works as a filter on visual if underlying column is included

[tim10]

Hello

I have 3 tables:

a) one is a disconnected table with a list of years : 2020, 2021, 2022.

In this one i have a measure with this code:

Selected RY = selectedvalue('Reporting Year'[ReportingYear])

b) one is a table with dates

In this one i have a measure with this code:

CY0 Delayed =
var yeartoday=year(today())

return
SWITCH(true(),
month(today())=2 && day(today())<=[Hide Show Day], yeartoday-1,
month(today())=1, yeartoday-1
,yeartoday
)

c) one is a table with financial data and a period column.

I've built a measure that has the code from this link: textfile

The purpose of the measaure is to hide/show some periods based on some date of today logic. 

If i apply this measure on a visual as a "filter on this visual" it only works if the underlying column, "Period", is included in the visual. Otherwise it will do nothing. 

Is there a way to remove the Period column from the table and have the filter still work? 

The reason i do not need this column in my visual is because it forces me to use a matrix (for grouping purposes) while the users want a table (to export to excel and analyze date). Because this column is in the table, when exporting to excel it separates the values on multiple rows (one for each period) and complicates analysis so i need it removed.

Thank you

[v-henryk-mstf]

Hi @tim10 ,

According to your description, if the created measure refers to the Period field, then deleting this field will cause the formula to fail. Could you try adding a filter condition to the formula, such as ALLSELECTED (table, table[Period])

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[tim10]

Hi,

Thank you for your reply. I tried your suggestion in the measure below. There is no error but calculations are wrong. Am I using it right? Have a nice day

Total Sales Calculation Current Year =

var selectedyear=[Selected RY]

RETURN

calculate(sum('table1'[TotalSales] ),filter('table1',YEAR('table1'[Period])=selectedyear),allselected('table1'[Period]))

[tim10]

Note: if the Period column is not included in the visual filtered by the measure above, all calculations are blank. Example of a measure that will be blank if the Period column is not included in the visual:

Total Sales Calculation Current Year =

var selectedyear=[Selected RY]

RETURN

calculate(sum('table1'[TotalSales] ),filter('table1',YEAR('table1'[Period])=selectedyear))