Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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?
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.
Is there any documentation about this new feature that explains how to format the attribute correctly?
Regards
Frederik
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |