cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
williamdasilva Frequent Visitor
Frequent Visitor

Calculated Column breaks Relationships

When adding a calculated column to the output values on a visual (grid), the relationship seems to be broken and a "full outer join" is applied between the two implicated tables.

 

Basic tables involved:

 

Records

  • RecordID (unique)
  • Name
  • Calculate field = COUNTROWS(Filter(Actions,Actions[Action Campaign ID] = "2016-2017" && Actions[Action Type]="Call Center"))+0

Tag

  • RecordID (link to Records table)
  • Tag

Actions

  • RecordID
  • Action Type
  • Action Campaign ID
  • ...

There are 1 to many relationships from Records to Tag and Call tables.

 

If I output data from Records and Tag, I get the correct result.

If I also output the calculated field, I will get every combination of distinct tag for every record, not just the ones actually in the tag table for that record.

If I remove the calculated field, all back to normal.

 

 

Am I using the calculated field incorrectly?

 

Thanks,

William

1 ACCEPTED SOLUTION

Accepted Solutions
williamdasilva Frequent Visitor
Frequent Visitor

Re: Calculated Column breaks Relationships

Obviously something wrong with that Calculated column.

 

I've opted instead for doing a query method. Duplicated the actions table, filtered on the ones I want to count, did a group by, then a merge back into Records table.

 

Not so elagant, but returns the correct results and does not drive me insane!

 

Would love to get that Calculated column working properly though.

 

Thanks,

William

View solution in original post

6 REPLIES 6
Vvelarde Super Contributor
Super Contributor

Re: Calculated Column breaks Relationships

@williamdasilva

 

Hi, change the Cross Filter Direction from Single to BOTH

 

FilterCross.png




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




williamdasilva Frequent Visitor
Frequent Visitor

Re: Calculated Column breaks Relationships

@Vvelarde

Thanks for the quick reply. Cross Filter to Both did not change anything. Below is what the relationships look like (Tag is actually called Assigned Appeals)

Screen Shot 2017-04-17 at 1.50.55 PM.png

 

 

Below is output from the Records table, filtered on one specific name. The column in blue is the calculated column.

Screen Shot 2017-04-17 at 1.51.27 PM.png

 

This is the Assinged appeal, filtered on the same record ID. 2 lines...

Screen Shot 2017-04-17 at 1.51.32 PM.png

 

When I add the calculated column from Records table, all of a sudden I get this!

Screen Shot 2017-04-17 at 1.51.38 PM.png

 

 

Thanks,

William

 

Vvelarde Super Contributor
Super Contributor

Re: Calculated Column breaks Relationships

@williamdasilva

 

The inclusion of all the other values is generated by the +0 at the end of your formula.




Lima - Peru
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




williamdasilva Frequent Visitor
Frequent Visitor

Re: Calculated Column breaks Relationships

@Vvelarde The +0 was there to avoid NULLS (or Blanks).

 

When I remove the +0, records that return NULL from the calculated field are excluded from the output!

 

Results without calcuated field

Screen Shot 2017-04-17 at 2.31.51 PM.png

 

results with calculated field (simply added the field)

Screen Shot 2017-04-17 at 2.32.12 PM.png

 

No filters...

Screen Shot 2017-04-17 at 2.33.04 PM.png

williamdasilva Frequent Visitor
Frequent Visitor

Re: Calculated Column breaks Relationships

Obviously something wrong with that Calculated column.

 

I've opted instead for doing a query method. Duplicated the actions table, filtered on the ones I want to count, did a group by, then a merge back into Records table.

 

Not so elagant, but returns the correct results and does not drive me insane!

 

Would love to get that Calculated column working properly though.

 

Thanks,

William

View solution in original post

v-huizhn-msft Super Contributor
Super Contributor

Re: Calculated Column breaks Relationships

Hi @williamdasilva,

I am very gald to hear that your issue got solved. Please mark your solution as answer, others will find workaround easily.

Thanks,

Angelia

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 95 members 2,799 guests
Please welcome our newest community members: