Forum Discussion

SuperSayan's avatar
SuperSayan
Icon for Resolver I rankResolver I
9 years ago
Solved

Data organisation and tables relationship

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?

Need Help
  • SivaMani's avatar
    SivaMani
    9 years ago

    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

  • SuperSayan's avatar
    SuperSayan
    8 years ago

    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-columns-must-have-unique-values-error-message/

     

     

    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

     

7 Replies

    • SuperSayan's avatar
      SuperSayan
      Icon for Resolver I rankResolver I
      9 years ago

      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's avatar
        SivaMani
        Icon for Resident Rockstar rankResident Rockstar
        9 years ago

        SuperSayan

         

        Do you have any common fields between,

         

        Feuil1 -> Liste TSC agences

        Liste TSC agences -> Valorisation

        Valorisation -> Feuil1