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.
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
Solved! Go to Solution.
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@Shinu1
Create the following measure, and assign it to the visual filter of the table visual and set to = 0
You can download the file: HERE
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" && NOT(_DATE >= DATE(2019,1,1) && _DATE <= DATE(2019,3,31)) && NOT(_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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy,
Is it possible that i do this workout without creating a measure just by creating relationships ?
I checked the output, the Const and Pro selection looks fine but the Org selection is showing incorrect output.
if Source = Altn and dates between 1st Jan 2019 to 31st Mar 2019 and 1st Jan 2020 to 31st Mar 2020 must not be displayed.
The Org output should look like the below table
Source | Date | Ext Rev |
GFS | 11/1/2019 | 182 |
GFS | 1/1/2020 | 102 |
NONGFS | 1/1/2019 | 151 |
Altn | 4/1/2019 | 127 |
Altn | 5/1/2019 | 199 |
Altn | 4/1/2020 | 197 |
Altn | 5/1/2020 | 122 |
Total | 1080 |
Regards
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Here is the link to PBI file
https://drive.google.com/file/d/1fUNtPDfoV0Z8QNpxkURRvNs8EgUqA8LU/view?usp=sharing
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |