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
alessio_missio
Regular Visitor

How to manage a hierachycal link between tables

Hi all, this is my first post, please be patient.

 

I need to build a Dataset that is giving me many problems.

I'm a newbie in Power BI that's why I'm here.

 

I've 2 tables:

  1. Table TGT - Contains defined target by country, region, subregion
  2. Table ORD - Containg Orders by country, region, subregion 

Let's have a look at the TGT table:

Country, TGT

Austria, 10

Germany, 40

Switzerland, 15

DACH, 80

 

and also the ORD table:

Country, ORD

Austria, 5

Austria, 1

Germany, 3

Germany, 10

Switzerland, 5

 

Please notice that I don't have any order with region DACH, and also please remember that DACH means Austria and Germany and Switzerland.

 

 

The problem is that I want to build a table with the following columns:

Country, Orders, TGT

 

 

There will be a FIlter by Region/Country and if the user will select Austria will get:

Country, Orders, TGT

Austria, 6, 10

 

for Germany:

Country, Orders, TGT

Germany, 13, 40

 

and so on... and here comes the tricky part, what will happen if someone will choose DACH ?

The output should be:

Country, Orders, TGT

DACH, 24, 80

 

Where 24 = 6+13+5 (sum of orders from Austria, Germany, Switzerland)

and 80 isn't the sum of the single targets by region, but is instead the value defined by region DACH.

 

So my measure should work this way: if I have a connection by lowest level (Country) use it, else use the upper layer (Subregion).

 

Please help me.

1 ACCEPTED SOLUTION
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @alessio_missio,

 

You could create the measure or the calculated column in TGT table like below.

 

Measure = IF(MAX('TGT'[Country])="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Column = IF('TGT'[Country]="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Here is the output.

 

output.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
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

1 REPLY 1
v-piga-msft
Resident Rockstar
Resident Rockstar

Hi @alessio_missio,

 

You could create the measure or the calculated column in TGT table like below.

 

Measure = IF(MAX('TGT'[Country])="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Column = IF('TGT'[Country]="DACH",CALCULATE(SUM(ORD[ORD]),ALL(ORD)),CALCULATE(SUM(ORD[ORD])))

Here is the output.

 

output.PNG

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.