Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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.
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
Solved! Go to Solution.
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]))
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]))
The result like this,
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.
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]))
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]))
The result like this,
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.
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.
Thanks again @v-zhenbw-msft!
Kind regards,
Jordan
Kingfisher Count Column =
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |