Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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
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 )
)
)
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?
Proud to be a Super User!
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
I had to change a little bit more. Look in the PBI file an try the filter if it what you want?
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])))
Proud to be a Super User!
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
Proud to be a Super User!
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.
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 🙂
Proud to be a Super User!
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.
Proud to be a Super User!
Update with screenshots and more info 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |