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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
StevenUser123
Frequent Visitor

Filtering A Visual Without Applying Filters

Hello,

I am trying to create a Comparison View between two sides, and am having trouble getting a common table to reference each side of the comparison. As you can see below, I have selected a total of "$1,080,571" but the below table is instead showing the full sales for that SKU instead of the filtered value.

 

The problem is applying the filter to make those numbers match results in an error with the other side of my comparison, since you can't reference two different SKU's from two different Accounts at the same time.

 

If there is DAX that can show a filtered value without the visual having a filtered applied, please let me know where I can read up on that capability. Otherwise, let me know if you might have a suggestion to fix this.

 

Thank you!

 

 Totals Mismatch.PNG

6 REPLIES 6
PawelWrona
Helper I
Helper I

I am not sure what you want to achieve here exactly, and probably it will be hard to guess without having a view into your data model.

 

Probably there are two main reasons why your values are not matching:

- When you select value from the table, it has specific filter applied which is not propagated to second visual (can be done with DAX or proper data model)

- There is at least one filter which is propagated from one visual to another, that should be removed using DAX

 

If you would like to share more information, maybe I will be able to help you.

Hi @PawelWrona ,

Appreciate the response. I am trying to achieve what you had mentioned there, selecting a value with specific filters that aren't being applied to the second visual. Would you mind advising which DAX might be able to filter to this value? Both visuals share the same table, which is it's own Dimension table hooked up to my Fact table.

 

Thank you!

Can you share calculation for "Current Sales Table 1"? Could you somehow mask the data and share pbix file?

@PawelWrona My formula is currently:

Current Sales Table 1 = CALCULATE('FACT_SALES'[Current Sales], FILTER('DIM_SKU',DIM_SKU[Product name]= SELECTEDVALUE('Comparison_Table 1'[Product name])))

And [Current Sales] from Fact_Sales is:
Current Sales = VAR EndDate=EOMONTH(DATE([EndYear],[EndMonth],1),0)
VAR StartDate=DATE([BeginningYear],[BeginningMonth],1)
RETURN
CALCULATE([Net Sales], DATESBETWEEN(DIM_DATE[Date],StartDate,EndDate))
 
I will work on removing data for a PBI file to share, but let me know if you may have an idea based on that formula. Thanks!

Could you provide the calculation for [Net Sales] as well? Still, pbix file would be the best option here.

 

And small off-topic here. Try not to use FILTER function within CALCULATE, especially when you pass entire table as an argument, it's a performance killer.

@PawelWrona Net Sales is:

Net Sales = CALCULATE(SUM(FACT_SALES[Value]),FACT_SALES[Attribute]="Net")
 
And understandable if you may need the model to troubleshoot this. Appreciate the headsup on the Filter function.
 
Thank you

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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