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
FrederikDec
Frequent Visitor

filtering detail rows out of higher level aggregated visualisations

Hi

 

We're using an SSAS Dimensional cube (with a live connection) in Power BI.

We have sales orders with multiple invoice lines. All these invoice lines are included in Fact Invoices because each line is from a different product etc...

 

We need to filter out the Invoices (invoice id) that have an amount of less than 30k. This is no problem when I take a list with the sum of the sales amount for each invoice id. This shows me only the invoice id's I want.

 

When I leave out the invoice id and have the sales amount aggregated for, let's say months or country, only the months or countries with sales amount > 30k are shown. Which is not unexpected but not what I need. I need to see the total amount for each month or country from invoices where the amount is > 30 k.

 

I tried adding the amount as an attribute together with the invoice id in the cube but the problem is you can not do any 'greater than' filtering on these attributes which is to expect. Also, adding measures as attributes is not exactly the way to go I presume.

 

I also made some 'buckets' of the sales amount (0-10k, 10-30k, ...) which would be a plausible solution - only filter on the necessary buckets. The problem with this is that everyone wants different buckets.

 

Is there any solution within Power BI itself or do I need to fix this in the cube (and how?).

 

Regards

 

Frederik

 

 

8 REPLIES 8
Phil_Seamark
Employee
Employee

Hi @FrederikDec,

 

The fact that you are using an SSAS Dimensional Cube (live connect) means you will probably need to solve these in your model.

 

The structures you are after are possible, but might better suited to an MDX forum.  I can help if you like, but would need to see a copy of your model to suggest the calculations and dimensional changes required.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello Phil

 

Unfortunately, I can not share the model with you due to NDA content but can you maybe point me in the right direction? A good MDX forum for example?

 

Regards

 

Frederik

I think this solves my problem:

 

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-feature-summary/#rangeSlicer

 

It's a new range slicer in the March 2017 update which filters (currently only based on attributes) numeric data in the detail rows.

 

Regards

 

Frederik

Hi @FrederikDec,

 

Great to hear the problem got resolved! Could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?Smiley Happy

 

Regards

Actually, in our case, the problem is not solved. The slicer does not seem to work with an attribute from a dimension in an SSAS cube.

After further investigation, it seems that the attribute we want to filter is not correctly recognized or in the wrong data format. The attached screenshot shows this.

afbeelding.png

Is there any documentation about this new feature that explains how to format the attribute correctly?

 

Regards

 

Frederik

Well Good Morning @Phil_Seamark Smiley Happy

Apologies image corrected Smiley Happy

 

NZ.PNG

Been at work for an hour too.  It's Wednesday morning too, so if you need any info from the future just ask.

 

I'm actually based in Wellington which is the same timezone as Auckland.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.