Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PMF99
Regular Visitor

Comparing Columns

Hi,

 

I have 2 tables in my data model & wish to calculate the difference between them

  • Append_All_Boards[Board TCV] & 
  • Resign_Append[Access TCV].

The tables are connected by the common field [Deal Number]. 

 

When I attempt to use this expression in my Append_All_Boards table =related(Resign_Append[Access TCV])...

 

I get the following error message.."The column 'Resign_Append[Access TCV]' either doesn't exist or doesn't have a relationship to any table available in the current context."

 

Can you help?

 

Many thanks

Peter

 

 

 

7 REPLIES 7
MartynRamsden
Solution Sage
Solution Sage

Hi @PMF99 

 

Can you confirm that you have an active many to one relationship between Append_All_Boards and Resign_Append?

Are you able to include a snip / screen shot of your Relationship view?

 

Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

Thanks Martyn.

 

Here you go ...

 

Capture.PNG

 

 

 

 

Hi @PMF99 

 

The RELATED function can only be used on the many side of a many to one relationship.

In your case, this is the Resign_Append table. Hence why you're seeing the error.

 

Can you explain in a bit more detail (ideally with some example data) what you're trying to achieve?


Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

Hi Martyn,

 

Here's the pivot I want to create....

 

Capture.PNG

[Board TCV] by [Deal Code] comes from Append_All Boards and gives the correct answer.

 

[Access TCV] by [Deal Code] comes from Resign_Appends and gives the correct answer.

 

My issue is I'd like to calculate the Var (variance) in Power Pivot and not manually calculate in Excel as I'm now doing.

 

Trying to bring the 2 columns together in 1 table to be able to calculate the variance in Power Pivot is what's giving me the problem.

 

Many thanks

Peter

 

 

Hi @PMF99 

 

In that case, I would advise that you create 2 explicit measures to calculate the sum of [Board TCV] and [Access TCV], as follows:

 

Total Board TCV = SUM ( Append_All Boards[Board TCV] )
Total Access TCV = SUM ( Resign_Appends[Access TCV] )

 

You can then use those measure within another measure to calcualte the variance.

Variance = [Total Board TCV] - [Total Access TCV]

 

You can then add all 3 measures to your pivot table to give you the result you want.

 

Hope this helps.

 

Best regards,

Martyn

 

 

Thanks Martyn.

 

But it needs to be by [Deal Code]. Will your suggestion achieve this?

 

Thanks

Peter

 

Hi @PMF99 

 

You'll need to include the [Deal Code] column from the 'Append_All_Boards' table on the rows of your pivot table.

The measures should then be calculated under the correct filter context.


If you have any trouble with it, please share a copy of your file (excluding any sensitive data).


Best regards,

Martyn

 

If I answered your question, please help others by accepting it as a solution.

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.