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 it as the column heading rather than the value from the fact table.

 

Measure is 

=VAR yearx =
    MAX ( 'Fact_Table'[Year_Cyear_Index] )
RETURN
    CALCULATE (
        [FTE_Sum_End],
        FILTER (
            ALL (Fact_Table'[Year_Cyear_Index], 'Fact_Table'[ayr],'MIT enrol_load_procedure_tbl'[cyear]),
            'Fact_Table'[Year_Cyear_Index]
                = yearx - 101
        )
          )

If we use Fact_Table'[cyear] as the column header the measure works, if we use dimension_table[cyear] it stops, I have no idea how to resolve so any suggestions appreciated, we have had to introdue the dimension table as we now have a second fact table that we need to introduce.

Need Help

5 Replies

  • itchyeyeballs's avatar
    itchyeyeballs
    Impactful Individual
    8 years ago

    I think I answered my own query by using an extra all:

     FILTER (
            ALL ('MIT enrol_load_procedure_tbl'[Year_Cyear_Index], 'MIT enrol_load_procedure_tbl'[ayr],'MIT enrol_load_procedure_tbl'[cyear],'MIT enrol_load_procedure_tbl'[va_date]),
            'MIT enrol_load_procedure_tbl'[Year_Cyear_Index]= yearx - 101
        ),
        all(Dim_course_year[Course_year_link])
          )

    However this stops working if I try to add an extra all() for a second dimension table

    =VAR yearx =
    MAX ( 'MIT enrol_load_procedure_tbl'[Year_Cyear_Index] )
RETURN
    CALCULATE (
        [FTE_Sum_End],
        FILTER (
            ALL ('MIT enrol_load_procedure_tbl'[Year_Cyear_Index], 'MIT enrol_load_procedure_tbl'[ayr],'MIT enrol_load_procedure_tbl'[cyear],'MIT enrol_load_procedure_tbl'[va_date]),
            'MIT enrol_load_procedure_tbl'[Year_Cyear_Index]= yearx - 101
        ),
        all(Dim_course_year[Course_year_link])
        ,all(Dim_date,Dim_date[Date_ayr])
          )

    gives - Multiple table arguments are not allowed in the ALL function.

  • 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