Forum Discussion
Use slicer value to filter a table
Hy everyone,
this is my first post here š I hope to write it in the right way
This is my problem.
I have a simple "Table 1 - Fatturato"
I want to create an other table "Table 2 - Fatturato progressivo" containing only first n months of the year.
I create a calculatetable, named TbProgressivo, and i got it.
This is the result i want.
But i got it inserting manually 2022 as year and 3 as month.
I want to read these 2 values (year and month) from respective slicers
With this measure, i can read the slicer value
But when i use it in calculatetable
Result is a empty table
I think this a relation problem or some issue with calendar table.
But i can't figure out...
This is the relations:
File pbix: https://drive.google.com/file/d/1inh92l7sQzFfXJYuu6D2scuRlvPoJWTK/view?usp=sharing
Thanks for the help!!
Giuseppe
Hi PSampras ,
To my knowledge, calculated table could not be dynamically changed via slicer.
If you want to dynamically filter a table... Iād suggest you put the columns into a table visual and create a measure that will return 1 for the rows you want to see and 0 for those that you don't. Then use the Visual-level Filter Pane to filter the rows by the measure. For example:
Flag Filter = IF(YEAR(MAX('Fatturato'[DatDoc]))=SELECTEDVALUE('Year Selection'[Year]) && MONTH(MAX('Fatturato'[DatDoc]))<=SELECTEDVALUE('Month Selection'[Month]),1,0)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
PSampras , if you select one month and want to display more than that then your slicer need to be on an independent date table
example
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1 *month(_max) ) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
I need to create a relation between Date and Date1?
Thank for your reply and your support.amitchandak
Your formula works great: using CALENDARIO2 as indipendent data table, i obtain the progressive value related to the month selected in slicer ("Fatturato progressivo").
6.632.458,12 is the sum of gen, feb and mar. Cool!But i can't understand how to use your kindly advice to obtain a table like this (Table2 in the figure)
I use:Table2 =CALCULATETABLE(FACT_PL_FATTURATO,CALENDARIO[Year]="2022" && CALENDARIO[MonthOfYear]<4)
and it works but, obviously, i have to replace 2022 and 4 with slicers values.
Thank you,
Giuseppe
Hi PSampras ,
To my knowledge, calculated table could not be dynamically changed via slicer.
If you want to dynamically filter a table... Iād suggest you put the columns into a table visual and create a measure that will return 1 for the rows you want to see and 0 for those that you don't. Then use the Visual-level Filter Pane to filter the rows by the measure. For example:
Flag Filter = IF(YEAR(MAX('Fatturato'[DatDoc]))=SELECTEDVALUE('Year Selection'[Year]) && MONTH(MAX('Fatturato'[DatDoc]))<=SELECTEDVALUE('Month Selection'[Month]),1,0)Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you Anonymous , it works!
