code behind query

[Debbie]

I'm new to Power BI and I'm trying to test a new simple tabular model.

I'm joining a dimension table to a fact table and another dimension table. There relationships are all 1:1. If I write the SQL directly in SQL Server Management Studio, I get the correct result (as expected from the source system client).

If I create a simple table visualisation in PBI - couple of data items from first dimension, a measure from the fact table and a couple of data items from the second dimension table, I get the wrong result - my fact table is picking up a different foreign key for the second dimension table. I have no idea why this is happening or how to troubleshoot. I've rebuilt & redeployed my model and refreshed my data source.

How do I see/check what Power BI is doing in the background so I can see where I am going wrong?

Debbie

[v-shex-msft]

Hi @Debbie,

You can refer to below blog to find out the SSAS instance which power bi used to store data model:

Connect to Power BI Desktop Model from Excel and SSMS

After above steps, you can connect this instance and test/debug like a normal SSAS instance.

Regards,

Xiaoxin Sheng

[Debbie]

This looks really useful - many thanks! I will pick my way through the steps.

[Debbie · Accepted solution]

So... I won't say I figured it out because it still doesn't make any sense, but I made the problem go away by setting properties on the primary key in the offending table - set summarize to DO NOT SUMMARIZE and row identifier to TRUE. This fixed the issue.

No idea why. None of my other dimension tables were giving this error, but I set the properties on all the primary keys for consistency.

Debbie