DAX filtering with Slicers through multiple one to many related tables

[Scott99]

Hi all,

First time posting but any help would be greatly appreciated.

I've reduced the model down to remove some complexities but still having issues pulling information correctly through tables. Schema is like a reversed star schema i.e. the fact table is the 1 on the one to many side relationship (this is the part i'm interested in):

Programme table (1) => (*) Project table

Project Table (1) => (*) Project Theme table

Represented below with dubby data:

Wider Schema

In the main report I am using a table with the main element being ProgrammeReference and as expected when I use a slicer containing anything from project it doesn't filter and so I need some DAX in order to retreive the data. When I add the following visual level filter on report it filters the report BEFORE the slicer is used which isn't what I want:

Linked project Cross filter slicer = CALCULATE(DISTINCTCOUNT(Programme[ProgrammeId]),CROSSFILTER(Project[ProgrammeId],Programme[ProgrammeId],Both))

So any help getting the report to filter only when the slicer is used would be very helpful.

Slicer: Linked Project Stage, 'Project'[Project status] 

However I also need a slicer to filter the main report using fields from the Project theme table, but only after the slicer has been used, I've attempted writing some DAX of my own but it simply removes all fields from the report e.g. the below did not work:

Project Theme Cross filter slicer =

if( ISFILTERED('Project Theme'[ProjectThemeType]) = TRUE(),

    IF( Project[Linked project Cross filter slicer] > 0,

    CALCULATE(DISTINCTCOUNT('Programme'[ProgrammeId]),CROSSFILTER('Project Theme'[ProjectId],Project[ProjectId],Both))))

I had envisioned some kind of Measure would be required to be added as a visual level filter to the main report but open to other solutions.

Let me know if more information is required!

Thanks in advance

Scott99

[tamerj1]

Hi @Scott99 
Please try

Linked Projects =
IF (
    ISFILTERED ( 'Project Theme'[ProjectThemeType] )
        || ISFILTERED ( 'Project'[Project status] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Programme'[ProgrammeId] ),
        CROSSFILTER ( Project[ProgrammeId], Programme[ProgrammeId], BOTH ),
        CROSSFILTER ( 'Project Theme'[ProjectId], Project[ProjectId], BOTH )
    )
)

[andhiii079845]

Can you please make your first problems more clear on a visual you used (e.g. matrix visual)? It is for me difficult to understand without the visual which has to be filtered. Perhaps it is my reading ability in english 😉

I need the context i which the measure will be used. 

However I also need a slicer to filter the main report using fields from the Project theme table, but only after the slicer has been used, I've attempted writing some DAX of my own but it simply removes all fields from the report e.g. the below did not work:

Do you mean what the result table show nothing if the filter is not used? 

[Scott99]

Hi andhiii079845,

Firstly thanks so much for taking an interest in my problem, I do very much appreciate it!

So the problem has 2 elements (assuming the image of the schema is fine).

Part 1: My table visual is based off of the programme table and uses Programme reference as the first entry, however having created a slicer using a field from the project table has resulted in the programme table not filtering as i'd like it:

 => 

As you can see in the above changing the 'Linked project Stage' filter doesn't filter the table. I'd like the filter to show only programmes that are related to projects at any given stage when the filter is used. 

Part 2: Similar to the above but using the project Theme slicer, I'd like that filter to be able to filter the programme table visual to only show programmes that have projects with (in this example) a major theme. I do not want the table to be filtered prior to using the slicers though, so essentially when no slicer is in use, we can see all programmes listed. Again as you can see in the below, using the slicer has no impact on the table visual.

 => 

Note: I can't make the programme (1) => project (*) relationship a both-directional one as this automatically filters the programme table visual.

Again if you need any further information, i'd be more than happy to provide.

Thanks

Scott99

[andhiii079845]

I had to change a little bit more. Look in the PBI file an try the filter if it what you want?

DAX filtering with Slicers through multiple one to many related tables REV2.pbix

I have to add separate slicer dimensional table and the new measure is:

Filterrprojectstatus = 

VAR _status =SELECTEDVALUE(StatusSlicer[Project status])
VAR _theme = SELECTEDVALUE(ProjectThemeSlicer[ProjectID])

VAR _bothfilter = (SUMMARIZE(FILTER(Project,Project[Project status]=_status && Project[ProjectID]=_theme),Project[ProgrammeID]))
Var _statusfilter = SUMMARIZE(FILTER(Project,Project[Project status]=_status),Project[ProgrammeID])
VAR _themefilter = SUMMARIZE(FILTER(Project,Project[ProjectID]=_theme),Project[ProgrammeID])

RETURN
IF(HASONEFILTER(ProjectThemeSlicer[Project Theme Type]) && HASONEFILTER(StatusSlicer[Project status]),countx(_bothfilter,Project[ProgrammeID]),
IF(HASONEFILTER(ProjectThemeSlicer[Project Theme Type]),countx(_themefilter,Project[ProgrammeID]),countx(_statusfilter,Project[ProgrammeID])))

[andhiii079845]

I try to generate with your information a PBI File but will perhaps miss one important point. Please take this and build your probem in this file and share it again. 
DAX filtering with Slicers through multiple one to many related tables.pbix

DAX filtering with Slicers through multiple one to many related tables.xlsx

[Scott99]

Hi andhiii079845,

Just had a look at the .pbix file and you've got it 100%. As in that .pbix, when you try to use the project status slicer the table below doesn't get filtered, which is what I'm after. The same goes for project theme type slicer, it isn't filtering the programme table. 

[andhiii079845]

Perfect! 🙂 But I do not use this "Linked project Cross filter slicer"? So the goal is that you can filter. i will try my best 🙂

[andhiii079845]

You have to show via screenshot what is the problem exactly. Its is difficult to help. In best case share your PBI file via onedrive, dropbox with some example data.

[Scott99]

Update with screenshots and more info 🙂