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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Relationship Help

Hi all,

 

Got a little conundrum I need some help with.

 

I'm currently building a reconciliation report that checks we're not losing any client records as we move data from one system to another. 

To do this, I need to create a centralised table that I can use to create visuals, which I've got as the below red circled table "Standardised Attribute Name w". 

 

The issue I'm having, is that as soon as I try and use the Values with a red line next to them, it throws off my counts. However with blue ones it works. 

 

The blue marked values work as they are unchanged from the data in the "Results LSC2 02 07 2020" table, however the red values have changes in them to allow a link to the other data sources "Results KFR 02 07 2020" in the bottom left and "Data source info" in the top right. 

image.png

 

A demonstration of this problem not working is below. 

 

"Standardised Attribute Name" works in every table I use it in (custom column built via a lookup), but the "Value" columns do not. The value columns that come from the "Reults LSC2 " data table all work, which can be seen in the below picture (1. and 2.). But as soon as I use a set of values that contain values used in other measures, it breaks (seen in 3. and 4. below). Red indicates a problem, blue indicates something that works as intended.  

image.png

 

Can anyone reccomend a fix here? I thought the "Landscape Value Bridge" would help the issue, but it's made no difference. 

 

File link here: 

removed, happy to be contacted for help if needed!

 

Kind regards,

 

Jordan  

1 ACCEPTED SOLUTION
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

The many-to-many relationship causes you to have the same value.

 

Maybe you need to create a landscape count column in each table.

 

In Landscape Value Bridge table, we create a column.

Landscape Column = 
CALCULATE(SUM('Results_LSC2 02 07 2020'[Landscape Count]),
FILTER('Results_LSC2 02 07 2020',
'Results_LSC2 02 07 2020'[Standardised Attribute Name]='Landscape Value Bridge'[Standardised Attribute Name] &&
'Results_LSC2 02 07 2020'[prefix]='Landscape Value Bridge'[prefix]&&
'Results_LSC2 02 07 2020'[Stand Value 1]='Landscape Value Bridge'[Stand Value 1]&&
'Results_LSC2 02 07 2020'[Stand Value 2]='Landscape Value Bridge'[Stand Value 2]))

 

R1.jpg

 

In Standardised Attribute Name Lookup w Value table we create a column too.

 

Landscapge column = 
CALCULATE(SUM('Results_LSC2 02 07 2020'[Landscape Count]),
FILTER('Results_LSC2 02 07 2020',
'Results_LSC2 02 07 2020'[Standardised Attribute Name]='Standardised Attribute Name Lookup w Value'[Standardised Attribute Name] &&
'Results_LSC2 02 07 2020'[Stand Value 1]='Standardised Attribute Name Lookup w Value'[Stand Value 1]&&
'Results_LSC2 02 07 2020'[Stand Value 2]='Standardised Attribute Name Lookup w Value'[Stand Value 2]))

 

R2.jpg

 

The result like this,

 

R3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

View solution in original post

2 REPLIES 2
v-zhenbw-msft
Community Support
Community Support

Hi @Anonymous ,

 

The many-to-many relationship causes you to have the same value.

 

Maybe you need to create a landscape count column in each table.

 

In Landscape Value Bridge table, we create a column.

Landscape Column = 
CALCULATE(SUM('Results_LSC2 02 07 2020'[Landscape Count]),
FILTER('Results_LSC2 02 07 2020',
'Results_LSC2 02 07 2020'[Standardised Attribute Name]='Landscape Value Bridge'[Standardised Attribute Name] &&
'Results_LSC2 02 07 2020'[prefix]='Landscape Value Bridge'[prefix]&&
'Results_LSC2 02 07 2020'[Stand Value 1]='Landscape Value Bridge'[Stand Value 1]&&
'Results_LSC2 02 07 2020'[Stand Value 2]='Landscape Value Bridge'[Stand Value 2]))

 

R1.jpg

 

In Standardised Attribute Name Lookup w Value table we create a column too.

 

Landscapge column = 
CALCULATE(SUM('Results_LSC2 02 07 2020'[Landscape Count]),
FILTER('Results_LSC2 02 07 2020',
'Results_LSC2 02 07 2020'[Standardised Attribute Name]='Standardised Attribute Name Lookup w Value'[Standardised Attribute Name] &&
'Results_LSC2 02 07 2020'[Stand Value 1]='Standardised Attribute Name Lookup w Value'[Stand Value 1]&&
'Results_LSC2 02 07 2020'[Stand Value 2]='Standardised Attribute Name Lookup w Value'[Stand Value 2]))

 

R2.jpg

 

The result like this,

 

R3.jpg

 

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ zhenbw

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

Anonymous
Not applicable

Hi @v-zhenbw-msft,

 

Perfect, thank you! I actually removed the bridge tables entirely and just created "Count columns" from each source in the "Standardised Attribute Name Lookup w Value" table in the middle of my star and it all works perfectly. Much tidier! I'll leave the updated measures at the bottom of this post for anyone who needs them. 

image.png

image.pngimage.pngThanks again @v-zhenbw-msft!

 

Kind regards,

 

Jordan   

 

Data Source Count Column =
CALCULATE(SUM('Data Source Info'[Data Source Count]),
FILTER('Data Source Info',
'Data Source Info'[Standardised Attribute Name]='Standardised Attribute Name Lookup w Value'[Standardised Attribute Name] &&
'Data Source Info'[Stand Value 1]='Standardised Attribute Name Lookup w Value'[Stand Value 1]&&
'Data Source Info'[Stand Value 2]='Standardised Attribute Name Lookup w Value'[Stand Value 2]))

 

Kingfisher Count Column =

CALCULATE(SUM('Results_KFR 02 07 2020'[Kingfisher Count]),
FILTER('Results_KFR 02 07 2020',
'Results_KFR 02 07 2020'[Standardised Attribute Name]='Standardised Attribute Name Lookup w Value'[Standardised Attribute Name] &&
'Results_KFR 02 07 2020'[Stand Value 1]='Standardised Attribute Name Lookup w Value'[Stand Value 1]&&
'Results_KFR 02 07 2020'[Stand Value 2]='Standardised Attribute Name Lookup w Value'[Stand Value 2]))

 

Landscape Count Column =
CALCULATE(SUM('Results_LSC2 02 07 2020'[Landscape Count]),
FILTER('Results_LSC2 02 07 2020',
'Results_LSC2 02 07 2020'[Standardised Attribute Name]='Standardised Attribute Name Lookup w Value'[Standardised Attribute Name] &&
'Results_LSC2 02 07 2020'[Stand Value 1]='Standardised Attribute Name Lookup w Value'[Stand Value 1]&&
'Results_LSC2 02 07 2020'[Stand Value 2]='Standardised Attribute Name Lookup w Value'[Stand Value 2]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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