DAX formula to filter fact table from dim table

[MichaelSamiotis]

Hi all,

I have created the below formula which doesn't filter the dim table and I get the same value for all the values including -1.

My fact table is the Fact_score and the dim table is the Dim_overall_experience. They are connected with 1=>* on Fact_score[Rate of the overall experience] = Dim_overall_experience[Overall_experience].

Count of overall experience =

CALCULATE (

    COUNT ( Fact_score[Rate of the overall experience] ),

    Dim_overall_experience[Overall_experience] <> -1

)

I amended it to the following which works very nicely but can't get my head around as to why the first one is not working as I am filtering the dim_table which is connected to the fact table. Am I missing something foundational here?:

Count of overall experience =

CALCULATE (

    COUNT ( Fact_score[Rate of the overall experience] ),

    Fact_score[Rate of the overall experience] <> -1

)

Thank you all in advance.

[MichaelSamiotis · Accepted solution]

I figured it just now. This has to do with filter context. When I filter with Dim_overall_experience[Overall_experience] <> -1 PBI replaces-overrides any filter in the current row with that, so it returns all rows from the fact table without -1 and does the same for each of the categories. When I put the FILTER function, it gives a subset of the table for each category and doesn't replace it on each one.

The below worked too:

CALCULATE (

    COUNT ( Fact_score[Rate of the overall experience] ),

    FILTER(Dim_overall_experience,Dim_overall_experience[Overall_experience] <> -1)

)

[Idrissshatila]

Hello @MichaelSamiotis ,

Can you make sure that these two tables have an active relationship in the data model view.

If I answered your question, please mark my post as solution so it would appeare to others, Appreciate your Kudos 👍

Follow me on Linkedin

[MichaelSamiotis]

Hi @Idrissshatila . Thank you for your reply.

The relationship is active as below with both data types being whole number.

With the formula

Count of overall experience =

CALCULATE (

    COUNT ( Fact_score[Rate of the overall experience] ),

    Dim_overall_experience[Overall_experience] <> -1

)

I am getting the below@

The field category in the Funnel graph is the column in my dim table and the Values is the measure.