Forum Discussion
Selection filter
Hi,
I have to make specific data selection from Raw data table based on Criteria table slicer.
RawData Table
| Source | Date | Ext Rev |
| GFS | 11/1/2019 | 182 |
| GFS | 1/1/2020 | 102 |
| NONGFS | 1/1/2019 | 151 |
| Altn | 1/1/2019 | 126 |
| Altn | 2/1/2019 | 125 |
| Altn | 3/1/2019 | 115 |
| Altn | 4/1/2019 | 127 |
| Altn | 5/1/2019 | 199 |
| Altn | 1/1/2020 | 137 |
| Altn | 2/1/2020 | 170 |
| Altn | 3/1/2020 | 176 |
| Altn | 4/1/2020 | 197 |
| Altn | 5/1/2020 | 122 |
Criteria table
| Source | Date | View |
| Altn | 4/1/2020 | Const |
| Altn | 5/1/2020 | Const |
| GFS | 11/1/2019 | Const |
| GFS | 1/1/2020 | Const |
| NONGFS | 1/1/2019 | Const |
| Altn | 4/1/2019 | Org |
| Altn | 5/1/2019 | Org |
| Altn | 4/1/2020 | Org |
| Altn | 5/1/2020 | Org |
| GFS | 11/1/2019 | Org |
| GFS | 1/1/2020 | Org |
| NONGFS | 1/1/2019 | Org |
| Altn | 1/1/2019 | Pro |
| Altn | 2/1/2019 | Pro |
| Altn | 3/1/2019 | Pro |
| Altn | 4/1/2019 | Pro |
| Altn | 5/1/2019 | Pro |
| Altn | 1/1/2020 | Pro |
| Altn | 2/1/2020 | Pro |
| Altn | 3/1/2020 | Pro |
| Altn | 4/1/2020 | Pro |
| Altn | 5/1/2020 | Pro |
| GFS | 11/1/2019 | Pro |
| GFS | 1/1/2020 | Pro |
| NONGFS | 1/1/2019 | Pro |
Understanding the "View" field built in Criteria table is:
Const: if Source = Altn then Criteria table does not have records with dates <= 31st Mar 2020
Org: if Source = Altn then Criteria table does not have dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020
Pro: All dates and All Sources are available
I created Many to Many relationship between tables based on dates and kept the relationship active. Cross filter direction both.
But when i select "Org" or "Const" from View slicer it does not affect the RawData table. Rawdata tables still shows the full data instead of data as per the dates in Criteria table
Please advise how do i achieve this.
Thanks
Shinu
Shinu1
Please use this measure:VisibleFlag = VAR _VIEW = SELECTEDVALUE(Criteria[View]) VAR _SOURCE = SELECTEDVALUE('Raw Data'[Source]) VAR _DATE = SELECTEDVALUE('Raw Data'[Date]) RETURN IF( _VIEW = "Const", IF(_SOURCE = "Altn" && _DATE <= DATE(2020,3,31),1,0), IF( _VIEW = "Org", IF( _SOURCE = "Altn" && ( (_DATE >= DATE(2019,1,1) && _DATE <= DATE(2019,3,31)) || (_DATE >= DATE(2020,1,1) && _DATE <= DATE(2020,3,31)) ) ,1 , 0) , 0 ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
7 Replies
Shinu1
Not quite clear about your requirement. Can you show the expected output, better if you could share a sample PBIX file?________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Here is the output that I'm looking for based on slicer selections:
View (Slicer contains three options)
Org
Const
Pro
When "Org" is selected, below is the output table. The commented rows must not be visible. The comment column is just for additional information. This field is not available in any table.
Source Date Ext Rev Comments GFS 11/1/2019 182 GFS 1/1/2020 102 NONGFS 1/1/2019 151 Altn 1/1/2019 126 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 2/1/2019 125 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 3/1/2019 115 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 4/1/2019 127 Altn 5/1/2019 199 Altn 1/1/2020 137 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 2/1/2020 170 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 3/1/2020 176 Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be visible Altn 4/1/2020 197 Altn 5/1/2020 122 When "Const" is selected, below is the output table. The commented rows must not be visible
Source Date Ext Rev Comments GFS 11/1/2019 182 GFS 1/1/2020 102 NONGFS 1/1/2019 151 Altn 1/1/2019 126 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 2/1/2019 125 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 3/1/2019 115 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 4/1/2019 127 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 5/1/2019 199 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 1/1/2020 137 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 2/1/2020 170 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 3/1/2020 176 Source = Altn and dates less than 31st Mar 2020 must not be visible Altn 4/1/2020 197 Altn 5/1/2020 122 When "Pro" is selected, below is the output table. All rows must be visible.
Source Date Ext Rev Comments GFS 11/1/2019 182 All data to be displayed GFS 1/1/2020 102 All data to be displayed NONGFS 1/1/2019 151 All data to be displayed Altn 1/1/2019 126 All data to be displayed Altn 2/1/2019 125 All data to be displayed Altn 3/1/2019 115 All data to be displayed Altn 4/1/2019 127 All data to be displayed Altn 5/1/2019 199 All data to be displayed Altn 1/1/2020 137 All data to be displayed Altn 2/1/2020 170 All data to be displayed Altn 3/1/2020 176 All data to be displayed Altn 4/1/2020 197 All data to be displayed Altn 5/1/2020 122 All data to be displayed Hope the above helps to understand my requirement
Here is the link to PBI file
https://drive.google.com/file/d/1fUNtPDfoV0Z8QNpxkURRvNs8EgUqA8LU/view?usp=sharing
