cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Report breaks with azure analysis service tabular cube

HI everyone,

I have a report using a tabular model from a azure analysis cube. When I select columns from different tables (3) the report breaks.

 

In detail, I have the following tables and columns:

  • Contract
    • ContractId int
    • PersonId int
    • ContractType varchar
    • FTE decimal
  • Person 
    • PersonId
    • Name
    • LastName
    • MaritalStatus
  • PersonAddress
    • AddressId
    • PersonId
    • Street
    • City
    • Country

 

A person can have many contracts and many addres, a contract can belong to one person only  as the same as address. 

 

When I select columns only between 2 of those tables like Person and PersonAddress   or Person and Contrac, I have no problems bu if I add anycolumn from a third table like:

 

Person.Name, Person.MaritalStatus, Contrac.ContractType, Contrac.FTE, PersonAddress.Country selecting do not summarize(because I wan a table with all records) for each column then the report breaks, if I select any calculation on FTE like average or sum it works.

 

But if I select varchar columns only like PersonAddress.Country, Person.Name, Contrac.Type then the report breaks but if i select first or last for any of the columns the report works. 

1 REPLY 1
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

Since "Person" table and "Contract" table have one to many relationship, 

"Person" table and "PersonAddress" table have one to many relationship,

"Contract" table and "PersonAddress" table have a indirect many to many relationship.

 

When you add columns from these two tables, it should be summarized for columns as we can't detemine many to many relationship.

For example,

user a have contact id 1, 2, 3

user a have address id x, y, z

we can't detemine which address id is associated with the contact id 1.

Only when we summarize the columns, it can show correctly.

 

Workarounds here:

To display columns across three tables,

Open Edit queries,

Create a copy of 'Person'  table

merge columns to 'Person'  from 'Contract' and 'PersonAddress', 

remove other columns.

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors