Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Userelationship wrong result with a used column

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 ( Dime...
DAX
USERELATIONSHIP
  • Anonymous's avatar
    Anonymous
    6 years ago
    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
Anonymous's avatar
Anonymous
Not applicable
6 years ago
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
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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?