Selection filter

[Shinu1]

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

[Fowmy]

@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]

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

[Fowmy]

@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]

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 · Accepted 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 🙂

YouTube  LinkedIn

[Shinu1]

Thankyou very much Fowmy. The output looks good.

[Shinu1]

Here is the link to PBI file

https://drive.google.com/file/d/1fUNtPDfoV0Z8QNpxkURRvNs8EgUqA8LU/view?usp=sharing