Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
MichaelSamiotis
Frequent Visitor

DAX formula to filter fact table from dim table

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.
1 ACCEPTED SOLUTION
MichaelSamiotis
Frequent Visitor

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)
)

View solution in original post

3 REPLIES 3
MichaelSamiotis
Frequent Visitor

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
Super User
Super User

Hello @MichaelSamiotis ,

 

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

 

Idrissshatila_0-1677062625836.png

 

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

Follow me on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Hi @Idrissshatila . Thank you for your reply.

 

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

MichaelSamiotis_0-1677063230902.png

 

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@
 
MichaelSamiotis_1-1677063321829.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.