Power BI Key Influences Error – A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it

The analysis is performed at the record level of the ‘table’ A field in explain by is not on the ” table or a table from the same data source and related to it by a many to one relationship. Try Summarising it

This model is all about behaviour and attitudes for an event company who put on IT events for companies who want to learn more about cloud based solutions.

The stakeholders are all the people who go on the courses, and those people who arrange the courses.

Its important for the company to know how their customers are reacting to their cloud based events and training. Which ones are doing well or not in terms of reactions.

The trainer or presenter sends out a questionnaire after ever event / course. they also send in their own questionnaire about how they felt the course was engaged with.

I have a Star Schema and in one of the dimensions I have a behaviours table. We want to know what influences behaviour from all the other attributes from around the star schema

There can only be a certain number of behaviour types so this dimension is set as a junk dimension. Each Type of behaviour is joined to the other using a cartesian Join

Every behaviour type is matched to every other behaviour type and then we can simply assign the correct row to the person event and theme

• So a stakeholder can go to many events
• A stakeholder can hold different behaviours for every event

We want to see what effects behaviour, so the Key Influences visual is the one we really want to use in this project

In this visual we are analysing by Explain by.

Add in a measure from the fact table and it works fine

But this isn’t telling us anything. We need descriptive information from the other dimensions

So lets go for something from Events

And here is where we get the error

The analysis is performed at the record level of the ‘table’

Obviously this part of the error makes sense because we want to understand the behaviour

A field in explain by is not on the ” table

That is correct. we are explaining by a field in another dimension

or a table from the same data source and related to it by a many to one relationship.

Try Summarising it

How to deal with this issue

the error doesn’t give much away but the solution lies in the model.

The recommended single direction join has been used on every single relationship. Therefore, the dimension can filter the fact but the fact cant filter the dimension.

And specific to this model, Another dimension cant filter another dimension

Events and behaviour cant filter eachother.

Unfortunately setting all the relationships to both didn’t resolve the issue either. Maybe the answer lies with DAX

Lets look at that error again

the Keyword in the error is SUMMARISE.

Now we know exactly what we want to use as the ‘Explain by’ Lets see if we can set up a Summary table using DAX

Key influencers Summarize table = 
SUMMARIZE (
    'Fact',
    'B & A table'[Behaviour],
    'B & A table'[Attitude],
    'Dim Events'[Event Title],
    'Dim Events'[Led by],
    'Stakeholder'[Name],
    'theme'[Theme]
)

To get around the fact the the dimensions are not joined to each other you simply start the summarise with the central fact table.

now we simply rejig to use the new summary table for this specific visual and we have a working solution

So when it comes to key influencers and star schemas, a great resolution is to create a summary table specifically for that visual