Visual breaks with fields from two DIM tables

[ferryberry]

Dears,

hope you can help me here, I'm getting nuts.

Below you can see my data model. It has two FACT tables, which are related to two DIM tables.

1. Relationships

- DIM Geo: one-to-many. To both FACT tables.

- DIM Materials: one-to-many. To both FACT tables.

I started building my visual. In the picture below, you can see fields from DIM_Materials (Material), and from DIM_Geo (Location). And only fields from one FACT table (turnover). Also some measures, which work flawlessly.

Problem comes when I start adding fields from the other FACT table (Materials). Everything gets upside down:

What am I doing wrong here?

Regards,

[Anonymous]

@ferryberry  Have you tried changing the cross filter direction between the 2 Materials tables to "both?" Is it necessary to break up the Materials table in to fact and dimension tables? 

[ferryberry]

Hi @Anonymous ,

Is it necessary to break up the Materials table in to fact and dimension tables?

I'd say so.

Have you tried changing the cross filter direction between the 2 Materials tables to "both?"

Like this? See below:

Now my visual breaks when dragging fields frm FACT_Materials (Error message "Can't display the visual").

Regards,

[Anonymous]

@ferryberry  It's not so easy to diagnose without seeing all the fields, but I would try consolidating the Materials table for starters.

[Anonymous]

@ferryberry  That looks like a relationship issue. I'd have to see what you're joining on. You might also need to modify the cross filter direction.

[ferryberry · Accepted solution]

Hi @Anonymous ,

I found a solution, and it has nothing to do with relationships. I keep my model as described.

I've implemented a SUM as @amitchandak suggested in this post:

Total Turnover = CALCULATE(SUM('02b. FACT_Turnover'[Turnover]),FILTER('02b. FACT_Turnover','02b. FACT_Turnover'[Material] in VALUES('02a. FACT_Materials'[Material])))

@amitchandak , now I'd like to do a running total (or cummulative sum). I've successfully added a Rank column, and I'd like to do this running total using this column. Following my data mode described in this post, what do you suggest? I've tried this formula, but no success:

Running Total =

VAR currank2 = [Rank99]

RETURN

    CALCULATE (

        SUMX('02b. FACT_Turnover',[Turnover]),

        FILTER(ALLSELECTED('02a. FACT_Materials'), [Rank99] <= currank2)

)

Thanks,

[ferryberry]

Hi @Anonymous ,

thanks for your quick reply. Relationships:

1. From DIM GEO to FACT Materials:

2. From DIM GEO to FACT Turnover:

3. From DIM Materials to FACT Materials:

4. From DIM Materials to FACT turnover:

Moreover, look at the visual below.

I have fields from both DIM tables, as well as both FACT tables (Actual Safety Days from DIM Materials, and Sum of Turnover, from FACT Turnover). In this case, "Sum of Turnover" is not a measure, but the same field "Turnover", which Power BI automatically summarizes by adding up.

Problem is I can't replicate this by a measure. Say SUM('FACTtable[Turnover]) breaks everything.

Any suggestion?

Thanks,