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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.