table visuals over a star schema without a metric displaying data relevant to other visuals

[DebbieE]

I am working with some beginner Power BI users who have started to create reports against a flat file and have created a lot of tables containing data.

They want to learn the correct modelling so want to change to a STAR schema. However there is an issue with this if you have a table containing data without a metric from the central fact table.

Here is a small example of one that works

The Table contains Date and Event description from the Date and Event Dimension

The Two other visuals also contain information from the two dimensions so the text relates to birthday which has been selected

In the next example that doest work we have a Stakeholder dimension, A Behaviour Dimension and a Date Dimension

Visual 1 is a table containing just behavior data and visual 2 is a table showing the stakeholder name and the metric

Note that no matter what stakeholder you select, you still see all the behaviours. there is no connection

To resolve the issue you can add the metric to the top table but I dont want to do this. How do you resolve this issue. Im fairly sure that the users will be really against using the star schema because of this problem and I want to have an answer ready

[MFelix]

Hi @DebbieE ,

Can you share a little bit on the model namely the columns and relationship between the tables, appear to me that you need to have an additional dimension table or using a measure to make the selection active or not.

Are you abble to share a mockupfile or if you have sensitive information share it trough private message. (Onedrive, google drive or wetransfer or similar links)

[DebbieE]

Unfortunaltly not

Each dimension has a surrogate key which connects to a surrogate key in the fact table

so when the table info just comes from behavior dim. and the other visuals contain info from the othr dimensions and the fact table, they dont affect the data in the behaviour dimension

Clearly its because its not creating the join from the center of the star schema

[MFelix]

Hi @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.

[DebbieE]

Im 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]

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. 

[DebbieE]

Unfortunalty I cant share the pbix so it may have to go unresolved for a while. 

So I should be able to have a table of data without the measure in it that reacts to the other visuals, that use different dimensions and measures from the fact table.

Just knowing that is at least something

[MFelix]

Can you share the measure syntax?

Can also be a question about the way you make the measure. 

[DebbieE]

Its not really about the measure in this instance

I just want to know if its possible.

So a table with three Columns from Dimension A

then a visual with a Column From Dimension B and a measure from Fact table

Dimension A and B are connected 1 to many to the fact table

In this case should clicking on the second visual have an effect on the first visual?

If its a yes, then I can do more digging with the understanding that it should work

[MFelix]

Hi @DebbieE ,

Answering you directly yes you can have a star schema and select values on a visualization and have another one filter out this is possible.

But on contrary to what you say this can be a problem with the measure, or with the connections in your model or with the simple setup of the visualization. Be aware that in PBI you have the row context and the filter context, measure don't use the row context so if you setup your measure incorrectly the interaction between the visualizations will not work.

I'm just trying to help you and achieve the best result but sometimes the theorical part does not match the models we have so we need to make ajustments to the connections or make additional columns or measure to have everything working properly.

Hope you can solve your issue and if you need anyfurther assistance please tell me.

[DebbieE]

Let me have another go.

The table contains description data from Behaviour dimension (A)

The next visual Contains a description from table B (Stakeholder type from the Stakeholder Dimension) .... and for example a Measure for Total Events DISTINCTCOUNT(EventID) in the fact table

EventID has no connection at all to table A because the Event ID joins to Event Dimension C

So in this case, If you click on Stakeholder Type Manager Should the initial table then change to show all the Behaviours for Managers?

[tex628]

Is there any relationship between table A and table B in your example?

/ J

[MFelix · Accepted solution]

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. 

[DebbieE]

thats absolutely what I needed

So you can get over this issue by aplpying a filter for Measure is not blank

fantastic

That will get me over the issue of users getting annoyed because they cant create their Table visuals without actually adding the measure to it

Thank you!!!!!

[MFelix]

@DebbieE ,

Sorry for being such a implicative and having so many questions but without understand the data is difficult to help out others as you for sure also know.

As refered there are a lot of workarounds depending on the way things are setup but talking about a simple model the easist way is to connect the table trough the common value in this case the measure that does no have blank values.

🙂 BTW Nice blog post

[DebbieE]

https://debbiesmspowerbiazureblog.home.blog/2020/07/29/power-bi-table-visuals-and-the-star-schema-en...

Here is my blog post in an attempt to get all this written up

[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?