Forum Discussion

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
8 years ago

Using dimension as header breaks all filter

Hi all,   We have hit a problem with our data analysis, we have been using a measure to calculate the value for a previous period, it was woking fine until we introduced a dimension table and used ...
Need Help
MFelix's avatar
MFelix
Super User
8 years ago

Hi itchyeyeballs,

 

Withouth having a sample of your data is difficult to pin point the error however you can check:

 

  • Is there a relationship between the Fact_Table and the Dimension_Table?
  • Are you using the Dimension_Table column as a context in your visual or still using the Fact Table column?

 

If you can supply a sample of the data would be easier to help you.

 

Regards,

MFelix

itchyeyeballs's avatar
itchyeyeballs
Impactful Individual
8 years ago

HI,

 

Thanks for the speedy response.

 

In my model I have 2 fact tables and several dimension tables (all linked correctly)

 

In my visual (a pivot grid) I have been using the fact table columns as the table headers, I switched to using the dimension table columns in order to introduce a second fact table and things stopped working.

 

My data looks like (I have simplified the fact tabe - it has more columns but only showing relevant ones)

 

Fact table:

Cyear

VaDate

FTE

cyear_index

 

Dimension1 

Cyear

 

Dimension2

Date_link

Date_ayr

 

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi itchyeyeballs,

     

    Can you please give me examples of the data for instance you have cyear is this 2018, 2017 or any other number?

     

    Just so I can make a mockup file easier.

     

    Thank you.

     

    MFelix

    • itchyeyeballs's avatar
      itchyeyeballs
      Impactful Individual
      8 years ago

      Hi, 

       

      Many thanks for your time, much appreciated.

       

      My basic challenge is to calculate progression rate, course year 1 16/17 to course year 2 17/18. I have created an integer index field which was allowing me to work out the right figure, however moveing to using 2 dimension tables has caused me issues with the filtering.

       

      I need to override the two dimension tables in order to work out previous year value so I can then find the proression rate

       

      Sample data:

       

      Sample output:

       

      I can possibly work around this by combining the two dimension tables into one but I'd love to know how to use the all() function in cases where multiple dimension tbles are used.