Forum Discussion
Need Help Making a Date Slicer Ignore Flagged Rows
Hi!
In Power BI, i have a table with two types of records, temporary records that vary by day, and permanent records that stay the same no matter the date. I need to be able to put a table and a date slicer on a page. I want the date slicer to impact the rows that are flagged 0 which are the temporary records but i want it to always show the records flagged 1 which are the permanent records no matter which dates are selected on the date slicer. I have not had any luck getting this to work
Ex Data: Basically i need my date slicer when filtered for 11/1/22 to show everyone (Tim does not have a date because he is a permanent record), and if the slicer has a different date it will still show Tim but not the others
| Name | Location | Flag | Date |
| Tim | Kentucky | 1 | |
| Joe | Boston | 0 | 11/1/22 |
| Sara | Tampa | 0 | 11/1/22 |
Hi MLUM
Please try this:
For better testing, I did some change to the data which you provided, here's the sample data:
Table:Then add a calculated table:
Table 2 = FILTER( VALUES('Table'[Date]), 'Table'[Date] <> BLANK() )Next, create a measure:
_Flag = VAR _Slicer = VALUES ( 'Table 2'[Date] ) RETURN IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Date] ) ), 1, IF ( SELECTEDVALUE ( 'Table'[Date] ) IN _Slicer, 0 ) )Finally, create a slicer with the field Table 2[Date] and replace the Table[Flag] with the measure[_Flag] in the table visual, the result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi MLUM
You could a measure like this to filter your visual.
Include = VAR _SelDt = MAX( 'Date'[Date] ) VAR _Result = IF( SELECTEDVALUE( 'Table'[Flag] ) = 1 || ( SELECTEDVALUE( 'Table'[Flag] ) = 0 && SELECTEDVALUE( 'Table'[Date] ) = _SelDt ), 1, 0 ) RETURN _ResultLet me know if you have any questions.
So the only issue with using a date table, is the filter im using is a modified date which is a date for all dates other than the current date. For current date it shows "Today", so it is a text field rather than an actual date field.
Thanks!
I would like to help but I'm a little unclear about your requirements.
Can you provide the following?
1) Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/14475233) Please explain how you would expect to get from step 1 to 2.
4) If possible, please show your past attempts at a solution.
A .pbix file with sample data would be best.
Hi MLUM
Please try this:
For better testing, I did some change to the data which you provided, here's the sample data:
Table:Then add a calculated table:
Table 2 = FILTER( VALUES('Table'[Date]), 'Table'[Date] <> BLANK() )Next, create a measure:
_Flag = VAR _Slicer = VALUES ( 'Table 2'[Date] ) RETURN IF ( ISBLANK ( SELECTEDVALUE ( 'Table'[Date] ) ), 1, IF ( SELECTEDVALUE ( 'Table'[Date] ) IN _Slicer, 0 ) )Finally, create a slicer with the field Table 2[Date] and replace the Table[Flag] with the measure[_Flag] in the table visual, the result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
