topic Re: Userelationship wrong result with a used column in DAX Commands and Tips

Userelationship wrong result with a used column

Anonymous — Sat, 09 May 2020 18:11:51 GMT

Hello ,

I have issues with Userelationship function.

In fact I have two measures :

Total_Amount_Direct =
CALCULATE (
SUM (Fact1[money] );
DATESYTD( 'Date_Table'[Date] );
FILTER ( Dimension; Dimension[Filter_Column] = "Physical")
)

Total_Amount_UserRelationShip =
CALCULATE (
SUM (Fact2[Amount] );
DATESYTD( 'Date_Table'[Date] );
FILTER ( Dimension; Dimension[Filter_Column] = "Physical");
USERELATIONSHIP ( Fact2[ID]; Fact1[ID] )
)

This is the result :

When I add the Filter Column to the table everything come wrong?

Is this the effect of the USERELATIONSHIP Function?

I need to add new Dimension ( copy of the existing Dimensio) to filter the table and have the good result?

Re: Userelationship wrong result with a used column

Greg_Deckler — Sat, 09 May 2020 23:53:42 GMT

Difficult to say what the problem is with the information provided. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Userelationship wrong result with a used column

Anonymous — Sun, 10 May 2020 13:00:19 GMT

Hello Greg,

This is my Schema :

I think when we want to across the dimension column returned from the USERELATIONSHIP then I cant see nothing as result

I think it's a DAX Logic

Re: Userelationship wrong result with a used column

Anonymous — Sun, 10 May 2020 19:45:17 GMT

Repeating myself... Please never, ever, ever join 2 fact table directly to each other. This is one of the worst things you can do in data modeling. Please use the correct design patterns unless you want to suffer in the future. 2 fact tables must always be joined to common (conformed) dimensions and slicing can ONLY be performed via the attributes of the dimensions, never directly on the columns of the fact tables.

If you deflect from this golden rule, you'll very soon start creating numbers nobody will be able to understand, including you.

There are plenty of vids on YT that explain how to build correct models. Please watch them before you shoot yourself in the foot.

Best
D

Re: Userelationship wrong result with a used column

Anonymous — Mon, 11 May 2020 16:49:28 GMT

Anonymous Greg_Deckler

The relation betwee my Fact tables is 1..1

In fact I solve it but it looks strange :

I have 3 measures :

Total_Amount =
CALCULATE (
SUM ( 'Fact1'[Amount] );
DATESYTD ( 'Date'[Date] );
FILTER (Dimension; LEFT ( Dimension[Filter_Column]; 1 ) <> "5")
)

Total_Amount1 =
CALCULATE (
SUM ( 'Fact'[Amount] );
DATESYTD ( 'Date'[Date] );
FILTER (Dimension; LEFT ( Dimension[Filter_Column]; 1 ) <> "5");
USERELATIONSHIP ( 'Fact1'[ID]; 'Fact2'[ID] )
)

Total_Amount2 =
CALCULATE (
SUM ( 'Fact'[Amount] );
DATESYTD ( 'Date'[Date] );
CALCULATETABLE (
SUMMARIZE (
Dimension;
Dimension[Dim_ID];
Dimension[Filter_Column]
);
FILTER ( Dimension; LEFT ( Dimension[Filter_Column]; 1 ) <> "5" )
);
USERELATIONSHIP ( 'Fact1'[ID]; 'Fact2'[ID] )
)

Relation between Fact1 and Fact2

1..*

Relation between Fact1 and Dimension

*..1

Column 1 and Column 2 are from Fact1 Table

There is no relation betwenn Fact 2 and Dimension .
There is no relation between Fact2 and Column 1 and Column 2 (we need to use the USERELATIONSHIP )

Result as follows:

The result is ok for the third measure using the CalculateTable, but I didn't Understand why the second Measure did not work !

Can Someone explain to me?