Forum Discussion

ferryberry's avatar
ferryberry
Frequent Visitor
2 years ago
Solved

Visual breaks with fields from two DIM tables

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...
Need Help
  • ferryberry's avatar
    ferryberry
    2 years ago

    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,
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
ferryberry
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

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