USERELATIONSHIP

[reiskleiton]

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

[MFelix]

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]

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]

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.

[reiskleiton]

Hi @MFelix 

Sorry, I made a mistake. 
You got it right. 

April - 100 (1 cliente from CAR)

May - 200 (2 clientes from CAR)

June - 300 (3 clientes from CAR)

Please, remember that the Excesso Limite is SUMX(

FILTER(CAR;CAR[Cliente] =Ordem Bloq[Cliente]);

CAR[Montante]

)

Do you need more information?

Thank you. 

[MFelix]

Hi @reiskleiton ,

Sorry for asking again, but can you break out the calcultion you are using? I understand the DAX code you place in but how is the result if you do it on a piece of paper?

What are the values you are considering for getting those values.

[reiskleiton]

Hi @MFelix 

Please, feel free to ask many questions you want.

Let me try do explain how I would do it by Power Query.

Here are the tables used

1. CAR - Group by [Mês e Ano] and [Cliente] and SUM [Montante]
   
2. Ordens Bloq - Group by [Mês e Ano] and [Cliente] and SUM [Valor da Ordem]
   
3. Add both to the visualization table
   
4. Then Merge (Right Outer) CAR with Ordem Bloq
   CAR[Cliente] = Ordem Bloq[Cliente]
   CAR[Mês e Ano] = Ordem Bloq[Mês e Ano]
   
5. After the Merge, I will keep all the Non Blank values from CAR[Montante] and add it to the visualization.

Hope it helps you.

[MFelix · Accepted solution]

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.

[reiskleiton]

Hi @MFelix

I was working on it and realize that there is an issue in the Total amount

It is 1.957.834,71.

Do you know what could it be?

Thank you.

[reiskleiton]

Hi @MFelix 

It worked \o/

I do appreciate your help. Thank you very much.

[MFelix]

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]

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