The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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
Solved! Go to Solution.
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.
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.
@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:
and it works but, obviously, i have to replace 2022 and 4 with slicers values.
Thank you,
Giuseppe
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
80 | |
77 | |
43 | |
39 |
User | Count |
---|---|
150 | |
116 | |
66 | |
64 | |
55 |