Data organisation and tables relationship

[SuperSayan]

Hello Everyone,

I'm starting with PowerBI and created a bunch of reports and now I'm facing a new challenge with my data.

I have a table of interventions with an intervention type (V01, V02,V05....) and another table that gives me a price for each type.

All of this works great with a 1 to many relationship.

Now I want to go further, each intervention is done by a technician and each technician belongs to a business unit (this information is also in another table with a relationship).

The challenge is the price of any intervention depends on the business unit and I'm struggling with the way to organise that.

Way around I can think of but not very pretty:

- create a concatenate column in the intervention table with business unit & type of intervention and use it as a primary key and link it to a table where I will have the value for each concatenation.

Is there another way to do this and organise better my data?

[SivaMani · Accepted solution]

@SuperSayan,

You're on the right path. Just set cross filter direction as both.

That's it

If you're not satisfied with this relationship, go for merge queries option in Query Editor.

Regards,

Siva

[SivaMani]

Hi @SuperSayan,

Can you share the snap shot of relationship which you have used?

[SuperSayan]

@SivaMani

This is the current model (sorry about the French).

So the Feuil1[Ressource] is linked to 'Liste TSC agences'[NomCpRM] and from this I can deduct the business Unit which is  'Liste TSC agences'[Région].

Then the Feuil1[Service] is linked to Valorisation[Code] which allows me to get the Price (Valorisation[CA (€)]) of each type of intervention.

However, I would like to have this "price" to change depending on the business Unit "Region".

In a database model, I would use 2 columns combined (Service and Region) as the primary key between Feuil1 and Valorisation the region info in this table so that I can get the "price" by region and by service. I don't know if that is feasible in PowerBI so my workaround is a new column with a concatenation which "emulate" this behaviour but I'm wondering if there is anything smarter to do and to avoid adding concatenate columns.

[SivaMani]

@SuperSayan

Do you have any common fields between,

Feuil1 -> Liste TSC agences

Liste TSC agences -> Valorisation

Valorisation -> Feuil1 

[SuperSayan]

@SivaMani

Feuil1 -> Liste TSC agences : Yes, Feuil1[Ressources] as a many to 1 relationship with Liste TSC agences[NomCpRM]

Liste TSC agences -> Valorisation: No

Valorisation -> Feuil1 : Yes, Feuil1[Service] as a many to 1 relationship with Valorisation[Code]

[v-ljerr-msft]

Hi @SuperSayan,

Have you tried the solution provided by @SivaMani above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?

If you still have any question on this issue, feel free to post here.

Regards

[SuperSayan · Accepted solution]

Hi @v-ljerr-msft @SivaMani

Sorry for coming back to you that late.

I was a bit struggling on this one and actually stumbled into the solution while looking for someting else.

Actually @SivaMani mentioned it in the second part of his message but I was a bit too ignorant on the query part to understand it.

Here is the step by step to create a table in the middle to tackle this issue.

https://blogs.technet.microsoft.com/cansql/2016/12/19/relationships-in-power-bi-fixing-one-of-the-co...

In addition to the above I needed to be able to access data in a table that was not directly linked to my working table.

In order to do that, I adapted the below formula to fit my needs: 

New Column = 
var JoinCol = 'TableA'[ID]
var NewCol = CALCULATE(MAX('TimeZone'[Value]),'TimeZone'[ID]=JoinCol)
return NewCol

[SivaMani · Accepted solution]

@SuperSayan,

You're on the right path. Just set cross filter direction as both.

That's it

If you're not satisfied with this relationship, go for merge queries option in Query Editor.

Regards,

Siva