Forum Discussion

Helper II

5 years ago

Solved

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/...

Need Help

Fowmy

5 years ago

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 🙂

YouTube LinkedIn

Fowmy

Super User

5 years ago

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 🙂

YouTube LinkedIn

Shinu1

Helper II

5 years ago

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
Helper II
5 years ago
Here is the link to PBI file
https://drive.google.com/file/d/1fUNtPDfoV0Z8QNpxkURRvNs8EgUqA8LU/view?usp=sharing
Fowmy
Super User
5 years ago
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 🙂
YouTube LinkedIn
- Shinu1
  Helper II
  5 years ago
  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
  - Fowmy
    Super User
    5 years ago
    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 🙂
    YouTube LinkedIn

Forum Discussion

Selection filter

Related content

Dynamic Filtering based on date selection

Filters not works while selecting multiple Period

Filter

Activator crashes when selecting Property Filter

Dynamic text based on filter selection