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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

Slicer not filtering Measure

Hello, 

I am trying to use a slicer as a pge filter however I have a series of calculated measures that are not filtering. The slicer is working fine for fields on the same table but my measures are pulling information from several other tables. I might be asking a sily question but how can I make this work?

Bachko_0-1680019015357.pngBachko_1-1680019085072.png

 

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

We would need to see your model and how the tables are related to each other, and the DAX in the measures that are unaffected. Post actual text for the DAX, and format it please using DAXFormatter.com - I cannot read that tiny image and cannot debug unformatted DAX like that.

Use this button in the forum post menu to post DAX code: 

edhans_0-1680020562162.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

If the Week Date in the slicer is coming from the OPS Table, it won't work. That table is on the many side of relationships and other tables filter it, but it filters nothing. You could turn on bi-directional filtering, but that is an extraordinarly bad idea. You need a true date table that filters all of your fact tables, then you add the Week date from the date table, and your model should be set up as a Star Schema. 

If you want an overview of a date table, see this blog. Create A Dynamic Date Table In Power Query — ehansalytics You can skip the part about making it dynamic. But without a doubt, you need a date table.

 

You should also get rid of all of those 1:1 relationships and merge those tables in Power Query. A 1:1 relationship is 99.9% of the time 1 table split in two. 

 

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi and sorry about that. Here the Codes:

 

 

Measure = (sum('ROLMON Table'[Food Sales Net])+sum('TB9S Table'[Food Sales Net])+sum('TBAC Table'[Food Sales Net])+sum('TBDE Table'[Food Sales Net])+sum('TBFH Table'[Food Sales Net])+sum('TBMON Table'[Food Sales Net])+sum('TBSC Table'[Food Sales Net])+sum('TLAC Table'[Food Sales Net])+sum('TLMON Table'[Food Sales Net]))/(SUM('ROLMON Table'[Beverage High Net])+SUM('ROLMON Table'[Beverage Low Net])+SUM('TB9S Table'[Beverage High Net])+SUM('TBAC Table'[Beverage High Net])+sum('TBAC Table'[Beverage Low Net])+SUM('TB9S Table'[Beverage Low Net])+sum('TBDE Table'[Beverage High Net])+sum('TBDE Table'[Beverage Low Net])+SUM('TBSC Table'[Beverage High Net])+sum('TBSC Table'[Beverage Low Net])+sum('TLMON Table'[Beverage Low Net])+sum('ROLMON Table'[Food Sales Net])+sum('TB9S Table'[Food Sales Net])+sum('TBAC Table'[Food Sales Net])+sum('TBDE Table'[Food Sales Net])+sum('TBFH Table'[Food Sales Net])+sum('TBMON Table'[Food Sales Net])+sum('TBSC Table'[Food Sales Net])+sum('TLAC Table'[Food Sales Net])+sum('TLMON Table'[Food Sales Net]))

 

 

Bachko_0-1680078521839.png

Bachko_0-1680078640523.png

 

Ok, I'll try to look at later. But if anyone wants to look at it here is the readable code formatted by DAXFormatter.

Test Measure =
(
    SUM ( 'ROLMON Table'[Food Sales Net] )
        + SUM ( 'TB9S Table'[Food Sales Net] )
        + SUM ( 'TBAC Table'[Food Sales Net] )
        + SUM ( 'TBDE Table'[Food Sales Net] )
        + SUM ( 'TBFH Table'[Food Sales Net] )
        + SUM ( 'TBMON Table'[Food Sales Net] )
        + SUM ( 'TBSC Table'[Food Sales Net] )
        + SUM ( 'TLAC Table'[Food Sales Net] )
        + SUM ( 'TLMON Table'[Food Sales Net] )
)
    / (
        SUM ( 'ROLMON Table'[Beverage High Net] )
            + SUM ( 'ROLMON Table'[Beverage Low Net] )
            + SUM ( 'TB9S Table'[Beverage High Net] )
            + SUM ( 'TBAC Table'[Beverage High Net] )
            + SUM ( 'TBAC Table'[Beverage Low Net] )
            + SUM ( 'TB9S Table'[Beverage Low Net] )
            + SUM ( 'TBDE Table'[Beverage High Net] )
            + SUM ( 'TBDE Table'[Beverage Low Net] )
            + SUM ( 'TBSC Table'[Beverage High Net] )
            + SUM ( 'TBSC Table'[Beverage Low Net] )
            + SUM ( 'TLMON Table'[Beverage Low Net] )
            + SUM ( 'ROLMON Table'[Food Sales Net] )
            + SUM ( 'TB9S Table'[Food Sales Net] )
            + SUM ( 'TBAC Table'[Food Sales Net] )
            + SUM ( 'TBDE Table'[Food Sales Net] )
            + SUM ( 'TBFH Table'[Food Sales Net] )
            + SUM ( 'TBMON Table'[Food Sales Net] )
            + SUM ( 'TBSC Table'[Food Sales Net] )
            + SUM ( 'TLAC Table'[Food Sales Net] )
            + SUM ( 'TLMON Table'[Food Sales Net] )
    )


Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

I womder if it has to do the fact that the Week Date is a sigle date for a full week and the data from the tables added are daily values.

 

If the Week Date in the slicer is coming from the OPS Table, it won't work. That table is on the many side of relationships and other tables filter it, but it filters nothing. You could turn on bi-directional filtering, but that is an extraordinarly bad idea. You need a true date table that filters all of your fact tables, then you add the Week date from the date table, and your model should be set up as a Star Schema. 

If you want an overview of a date table, see this blog. Create A Dynamic Date Table In Power Query — ehansalytics You can skip the part about making it dynamic. But without a doubt, you need a date table.

 

You should also get rid of all of those 1:1 relationships and merge those tables in Power Query. A 1:1 relationship is 99.9% of the time 1 table split in two. 

 

Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
Microsoft Guidance on Many-To-Many Relationships
Microsoft Guidance on Bi-Directional Relationships
Microsoft Guidance on Importance of Star Schema



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Anonymous
Not applicable

AMazing I'll get cracking on it many thanks!!

edhans
Super User
Super User

We would need to see your model and how the tables are related to each other, and the DAX in the measures that are unaffected. Post actual text for the DAX, and format it please using DAXFormatter.com - I cannot read that tiny image and cannot debug unformatted DAX like that.

Use this button in the forum post menu to post DAX code: 

edhans_0-1680020562162.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors