Exclude Certain Values from Filter based on logic from Disconnected Table

[CaveOfWonders]

Hi All,

Struggling with this one.

Please use this link (PBIX File ) to download the pbix file for ease of understanding/answering my question.

I have 3 dimension tables and one fact table

Dimension:

'Date'
'Projects'
'Display'

Fact:

'Fact Table'

My report uses the [Reporting date] (from the 'Date' table) as a drop down filter for my fact tables. There is a one to many relationship between 'Date' and 'Fact Table'.

I also have a 'Project' dimesnion table with a one to many relationship with my fact table using [Project ID] which is also used to filter all fact tables.

I have another table called 'Display'. This table has a list of all (many) [project id]'s and (many) [reporting date]'s and an additional column called [Display Flag] which contains 1's and 0's. 1 means display and 0 means do not display.

Question is, how can I filter out dates from my drop down filter on my report page that are 0.

So, if the display flag is 0 I want to exclude that reporting period from my reporting date drop down filter, if it is 1 then show the date.

Thank you in advance.

[CaveOfWonders]

Thank you so much dude 🙂🙂🙂🙂🙂🙂🙂🙂🙂🙂🙂

[SpartaBI]

@CaveOfWonders my please friend 🙂
Hey, check out my showcase report - got some high level stuff there 🙂
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[mh2587]

use the flag on page filter 

[SpartaBI]

@CaveOfWonders why don't you just connect it like this and put a filter on that column that is 1:

Because that also affect the fact table? That's why?

[CaveOfWonders]

I only shared a snippet of the model to help with finding a solution. We have over 12 (very large) fact tables connected to the model, along with RLS tables connected to the project table. Using bi-directional filtering puts your model at risk of ambiguity, and slow measures. It is not recommended hence not using it. It's an easy solution, but not the correct one. Thank you.

[SpartaBI]

@CaveOfWonders yes I know that 🙂 but you can use it if you know what you are doing. Didn't know about youe model before, just by the pic, what I showed will not cause ambiguity :).
You can make this a one direction and just create a measure like 
CALCULTE( COUNTROWS(Reporing Periods), CROSSFILTER(Published Dashboards[ReportingDate], Reporting Dashboards[ReportingDate], BOTH) and add it to the visual level filter of the slicer to be greated then 0

[CaveOfWonders]

Thank you @SpartaBI . Appreciate your help on this. Does that measure also take into consideration the Project being selected? There are 2 conditions to be met, one is the project id's have to match and the second is the dates have to match and then the flag has to be 1, i.e. yes.

[SpartaBI · Accepted solution]

@CaveOfWonders it's hard to understand 🙂 It depends on where you select and choose filters on the projects. If it's on the dim, then of course no but you can achive it by adding to my measure something like:
CALCULTE( COUNTROWS(Reporing Periods), CROSSFILTER(Published Dashboards[ReportingDate], Reporting Dashboards[ReportingDate], BOTH), TREATAS(VALUES(Projects[[Project ID]),  Published Dashboards[Project ID]).
You need to test it, I doesn't have all the data on your model. Hope that the answer you need.

[CaveOfWonders]

Okay, thank you :). I will test and get back to you with the resutls. Hopefully it works. I've been struggling with this all afternoon.

@SpartaBI 
Tested: Not Working. Getting the following error:

[SpartaBI]

@CaveOfWonders you need to create that relatinship I showed in my original picture of course 🙂 and it can be one direction because we will use that measure on the visual level filter of the slicer.

[CaveOfWonders]

@SpartaBI I created the relationship as advised:

Measure being used:

measure = 
CALCULATE (
    COUNTROWS ( 'Reporting Periods' ),
    CROSSFILTER ( 'Published Dashboards'[Reporting Date], 'Reporting Periods'[ReportingDate], BOTH ),
    TREATAS ( VALUES ( Projects[Project ID] ), 'Published Dashboards'[Project ID] )
)

but not working as expected. Project 7 should only have the 14th April and 9th June in the filter. The measure is counting a 1 for them all.

[SpartaBI]

@CaveOfWonders The measure is only meant to put on the visual level filter of the slicer. 
Also, regaring the other filter, is it applied:

[CaveOfWonders]

When I apply that filter to the page filter nothing appears in the drop down.

[SpartaBI]

@CaveOfWonders  I will PM you

[SpartaBI]

@CaveOfWonders can you PM me now, for some reason I don't have a message you button on your profile

[CaveOfWonders]

I think I've just resolved it now (with your help of course). I added a relationship to the project table and excluded 0's from the display flag column in power query and it seems to have done the trick. I can't thank you enough. Oh also, I added = 1 instead of > 1 in the filter.