Forum Discussion
Community Championtable visuals over a star schema without a metric displaying data relevant to other visuals
Hi DebbieE ,
Assuming that when you refer to stakholder is the person table on your image from the model setup correct?
In the attach PBIX I have made the following setup:
As you can see all table are related with the fact table only.
The measure Total events is:
Total Events = DISTINCTCOUNT('Fact'[EventID])I have setup a table visualization with only the description and another with the name and the total events.
As you refer if I setup the model only like this I will not be abble to filter out the behaviour with other visualization because you are only picking up the disconnected value.
However if you do just a simple change in your setup of the table it will do the job. Add the measure that you created on the filter pane of the behaviour and select different from blank and everything will filter out.
Again is the row vs filter context and the way you setup your visualization at work see image below and PBIX attach.
This can have a more complex scenario if your measures and visualizations are different based on the models so some simple tweaks.
Hope his is similar to what you need.
Super UserHi DebbieE ,
What columns are you using on your visualizations?
Be aware that using a star schema like this the columns to be used in the visualizations and on the measure should be the ones on the one side of the relationship one good practice is to hide the columns no the many side of the relationship, for example
on dates you should keep visible the Date column and hide the Date on the fact table.
Community ChampionIm really not sure what you mean
I have made sure that everything is a 1 in the dimension and a many in the fact table which is the recommended way of setting up a STAR Schema
- MFelix
DebbieE ,
On this type of schemes you need to always select the one side of the relationship for your visual meaning:
Calendar Table
Date Date1 Date2 Date2 Fact Table
ID Value Date 1 10 Date1 2 50 Date2 3 10 Date3 4 30 Date1 5 40 Date2 6 50 Date3 7 60 Date1 On the two table above you would create a relationship with the following setup:
Calendar [Date] 1 -> * Fact[Date]
One to many from Calendar to Fact table
In this case you should use the Calendar[Date] column on your visualizations and not the Fact[Date] so has a good practice you should hide the Fact[Date] column so you would not be tempted to use it.
So when I say if you are using only the one side of the table is are you using from the dimension tables or from the fact table.
Has you can see on the attach PBIX file I have simple star schema with 5 table but the funcitons are the same, I have a table that calculate the number of lines of the fact table and then the make the calculations based on the columns of the one side.
The only change was that the Event has a both side filtering in the relationship but this can be a problem because the one has you can see on the first table (final result is incorrect) in this case I should use the count of events from the fact table.
Please tell me if this model is similar to yours and if it helps.
- DebbieE
No problems.
I already understand all that and always doe that for every model
- MFelix
Have you checked the PBIx file I have attach?
Has you can see the filter in all dimension qorks no matter the dimension is on the table or not.
Is the model similar to ypurs?
- DebbieE
Because its just a test the logic is alot more simple.
I clearly need to go back to the drawing board on this one. I have no idea whats wrong. basically your saying that I should be able to do this without any problems?
- MFelix
Hi DebbieE,
Excuse for insisting but if I don't have further information is difficult to help you or guide in the correct path.
Can you share trough private message the file or a mockup. Or can you make a model in Excel with simulated values in the tables in order I can have a model similar to yours and help you.
I know that can be sensitive data that is why I asking it privately or a mockup.