Can't linked a summarised table

[AndrewZ]

Hi

I've created a summarised table as I don't need all the detail to calculate a few averages but now I can't link that newly created table to my existing tables. PowerBI gives no error it just lets me add the relationship, says working and then returns without the link appearing.

If I click save nothing happens and the realtionship does not save. I can't find anything about this. Those tables link fine to the source table why not to the summarised one?

Thanks

[v-achippa]

Hi @AndrewZ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @Deku for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[Deku]

Read this article the model adds a unseen blank row to allow to you to still see numbers even if a relationship doesn't have full referential integrity 

[Deku · Accepted solution]

Might be worth trying to create the relationship with TMDL view or Tabular Editor? 

[AndrewZ]

That helps, it won't let the relationship save because there is a circular refrence. I don't think there is but I believe I can see why it thinks there is as one of the calculated columns makes use of the PPA Secenario table. The strange thing is that table connects fine its only when I try summarise it that the connection fails.

[Anonymous]

Hi @AndrewZ ,

Based on the information, check if there are blank values inside the columns? Did you do something else before creating the relationship?

You can view the following links to learn more information.

Solved: Cant connect my summarize table to another table (... - Microsoft Fabric Community

Solved: Link Summary table to main data - Microsoft Fabric Community

Solved: Join two Summarized Tables - Microsoft Fabric Community

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Deku]

Is the table calculated with DAX. Can you share the definition 

[AndrewZ]

Yes the unlinkable table is calculated with dax 

WC Movement = CALCULATETABLE(SUMMARIZE('Total Investment','Total Investment'[cre0b_requestid],'Total Investment'[cre0b_ppascenario], 'Total Investment'[Attribute] ,"Entity Currency", SUM('Total Investment'[Running Total with Correct Signage])))

It has no calculated columns of its own. 

So as a work around I found if I duplicated the PPA Scenario table I was trying to link above and linked that it worked and then I linked the duplicate PPA Scenario to the original and it all worked, showing me its not some sort of dependancy but a PowerBI bug.

[Deku]

Addcolumns(

Allnoblankrow(

'Total Investment'[cre0b_requestid],

'Total Investment'[cre0b_ppascenario],

'Total Investment'[Attribute] 

),

"Entity Currency", 

CALCULATE(SUM('Total Investment'[Running Total with Correct Signage])

)

)

This should work. SUMMARIZE,  VALUES and ALL considers blanks row. ALLNOBLANKROWS and DISTINCT does not.

This can cause circular dependancies which means you can't create the relationship.

[AndrewZ]

I tried this and got the same thing. Made the link, saved it and nothing happened. I didn't think it was blank values anyway as there were none. PowerBI doesn't even give an error message it just returns without doing anything which points to it being an unhandled exception. Then there is the fact that you just need to make a copy of the table to get a connection working which is bizarre.

To be more clear on that I have three satellite tables that allow users to select scenarios, project and year. The scenario project is really small and looks like this

The calculated table actually only has -1,0 and 1 (never blank). Now if I connect this column it fails but if I make a duplicate of this Scenario table and connect that table to the calculated one it works. Then all I have to do is link the first and second scenario tables and I get a working model. If there was a dependency error that wouldn’t work but the fact that it does makes me think bug but I’m hoping someone here can point out something I’ve missed so my model doesn’t have to have unnecessary tables.

I just unlinked the original table from everything else and it still won’t link to the new table. This is ridiculous.