cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AFAmores Occasional Visitor
Occasional Visitor

Measures with tables related by multiple fields

Hi!

I have two tables ("a10" and "a64") related by multiple fields ([geo], [nace_r2], [na_item], [unit], [time]). Table a64 has more rows than table A10 (more values for [nace_r2] and [na_item]). I need to check the consistency of the filed [value] (available in both tables) for the rows with the same codes in the related fields in both tables. Moreover, there are blanks in both two tables. Therefore, to check the sums at different levels between the two tables, I need to exclude from the sums the related rows of both tables the rows where there is a blank in one or other table). 

I have already tried these four unsuccessful strategies:
1. to establish NON-active relationships for each related field among the two tables and apply multiple USERELATIONSHIP or CROSSFILTER.

2. Actually a10 and a64 are linked through a dictionary for each field [geo], [nace_r2], [na_item], [unit], [time].

3. Create in both tables a column of id as [time] &"_"& [geo] &"_"& [nace_r2] &"_"& [na_item] &"_"& [unit]. Linking both tables trough the id.

The latter works imperfectly: I can list the matched results but the summary metric (to analyze the deviations dynamically acording to the filters of the report is the percentual deviation of the value in a64 over a10) does not work properly because what is being reported is a sum of the percentual deviations of every row, what makes no sense).


I have created the following measures:

At table a64: 

SumT03=CALCULATE(SUM(nama_a64_BI[value]),FILTER(nama_a64_BI,nama_a64_BI[value]<>BLANK()),USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id]))


At table a10:

SumT01 =CALCULATE(sum(nama_a10_BI[value]),FILTER(nama_a10_BI,nama_a10_BI[value]<>blank()), USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id]))


%T3/T1 = abs(CALCULATE(divide([SumT03],SumT01])-1, USERELATIONSHIP(nama_a10_BI[id],nama_a64_BI[id])))


I am reporting in the visuals %T3/T1.


Any support would be much appreciated.

Thanks before-hand!

Best regards,

Antonio


PS: I also thought: Same as 3. but creating also a table with a single column id = [time] &"_"& [geo] &"_"& [nace_r2] &"_"& [na_item] &"_"& [unit] and linking a10 and a64 to the new table through the id. Does this makes any difference considering that a10 contains same rows as the new bridge?

1 REPLY 1
V-lianl-msft Senior Member
Senior Member

Re: Measures with tables related by multiple fields

Hi @AFAmores ,

 

Based on your description,Your problem is "the summary metric does not work properly". You could try to use "SUMX" instead of "SUM" to calculate measure.It is very difficult to analyze without looking at the data and just by imagining. See if you can paste the image of the chart representing any sample data. You could see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Best Regards,

Liang

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)