Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have 3 tables one has Pre Days means 1,2,3,4 and 2nd has post dates. I have a calender table which has some hard coded dates but I want to change start and end dates by appending the Predates in start and EndDates in end.
@Mujahid -
Calculated Tables and Calculated Columns are not affected by slicers.
You can filter the Date Table by selections in your slicers.
One way to implement this:
1. Create a Measure that will be BLANK for rows that should not appear, and "" for rows that should appear.
2. Add that measure to a table visual, along with your date.
3. If you want to hide the measure column, change the header name a single character and decrease the width of the column, so it will not appear.
The measure could be something like this:
Include Date Range = var minDate = <Your Logic> var maxDate = <Your Logic> var dateToAssess = MAX('Date'[Date]) return IF( dateToAssess >= minDate && dateToAssess <= maxDate, "", BLANK() )
I have multiple Start and End dates not a single. Initially I planned to create a table with calender and Union all dates. If If I use this measure so is it possible to append more filters with more min and max dates.
@Mujahid - I'm not sure exactly what you're trying to do, but here are some tables and measures that will hopefully be a good demo of the possibilities:
Create 2 Date Tables:
Date1 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
Date2 = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))
Create a Number table for adding to the dates:
Numbers = GENERATESERIES(0,100,1)
Then a Measure to limit the dates:
Include Date Range = var minDate = MIN('Date2'[Date]) + max(Numbers[Number]) var maxDate = MAX('Date2'[Date]) + max(Numbers[Number]) var dateToAssess = MAX('Date'[Date]) return IF( dateToAssess >= minDate && dateToAssess <= maxDate, "", BLANK() )
Here is the result (you need to Edit Interactions, so that one Number slicer will affect one table visual:
Can you please Share Pibx
I ran that file. It's truncating days dynamically as I select something.
My Case is bit different.
For Instance I have a table for Sales data. which has field name [Sales_Date] of multiple years. We have easter date slots (for 30 days each slot) for each year means start date of season and end date of season. So I created a table as you create with calender function and used union to uniform all years data means union(calender (start date, enddate),calender (start date, enddate),.....).
Then I created a relationship between date table and sales table. All things working fine at this stage.
Now Next thing is to add a visual if user want to see sales data preceeding N Days and Post N Days with eastern. Like it's 04/01/2019 to 04/30/2019 so user can see data for 03/26/2019 to 04/30/2019 so N Days preceeding 4. because User want to see N Days before easter with easter season.
Where I used technique mentioned about but It did not work by slicer.
If there is an other work around do let me know If i am using invalid strategy to cope up this issue.
Thank you
We need two slicers. One for Preceeding Easter and One for Post Easter.
@Mujahid you cannot pass slicer value when creating tables by dax.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |