Forum Discussion

reiskleiton's avatar
reiskleiton
Helper II
5 years ago
Solved

USERELATIONSHIP

Hi all, 

 

I have tried to get data from one table based on another by using USERELATIONSHIP function.

 

I need to get all the data that matches from CAR and Ordens Bloq tables on columns [Mês e Ano] e [Cliente].

 

 

I have a mesuare called [Montante PDD] 

Montante PDD = SUMX(FILTER(CAR;[Aging PDD]>=120);CAR[Montante]) and it works just fine.
Then I gotta perform the same mesaure but all Cliente that match from Ordem Bloq table. I tried to create it by using USERELATIONSHIP, but it gives the same wrong output

Excesso PDD =
CALCULATE(
[Montante PDD];
USERELATIONSHIP(CAR[Cliente];'Ordens Bloq'[CLIENTE])
)
 

How should I filter column CAR[Cliente]=Ordem Bloq[CLIENTE]

 

Thank you

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi reiskleiton ,

     

    Looking at the data you have given beliw you can do it adding the following measure:

    Excesso de limite = 
CALCULATE(SUM(CAR[Montante]); CAR[Cliente] in VALUES('Ordem Bloq'[Cliente]))

    Check result in attach PBIX.

11 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi reiskleiton ,

     

    Your issue is that you have a many to many relationship between both you tables, this returns more than one result for each side.

     

    I don't know if you have a dimension table with unique [Cliente] but if you create a dimension table with [Cliente] and then make a one to many with the other to tables (has you have calendar table) then you can simply make the direct sum without the need for USERELATIONSHIP.

    • reiskleiton's avatar
      reiskleiton
      Helper II
      5 years ago

      Hi MFelix 

      Yes, I do have a Cliente dimension table.

       

      How should I SUM CAR[Montante] by Filter CAR[Cliente] = Orden Bloq[CLIENTE] AND both same Calendario[Mês e Ano]?

       

      Thank you 

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    Hi reiskleiton ,

     

    This has to do with context, so you need to do one of two thing, or to add the cliente to your visualization or make the SUMX based on the Cliente.

     

    can you share a mockup of your data?

    • reiskleiton's avatar
      reiskleiton
      Helper II
      5 years ago

      Hi MFelix 

       

      I tried to explain how it should work.

       

       

      It could be done in Power Query by Merge (Right Outer) CAR[Cliente] = Ordem Bloq[Cliente] AND Calendário [Mês e Ano], but I want to avoid to create a unecessary table to my model.

       

      Thank you for your help.

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Hi reiskleiton ,

         

        Can you please explain how the calculation for the last column is done.

         

        If I look I have the following data:

        April - 3 customers in CAR with a total of 300 but only 1 cliente in Bloqueado on that date with the value of 200.

        June - 3 customers in CAR with a total of 300 but 5 cliente in Bloqueado on that date with the value of 1000. your total value is 100

         

        Can you give a little bit more detail please.