Using dimension as header breaks all filter

[itchyeyeballs]

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.

[MFelix]

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]

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]

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]

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.

[itchyeyeballs]

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.