Forum Discussion
Exclude Certain Values from Filter based on logic from Disconnected Table
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 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.
16 Replies
Thank you so much dude 🙂🙂🙂🙂🙂🙂🙂🙂🙂🙂🙂
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 🙂
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?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.
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
use the flag on page filter
