2

SQL Server Analysis Services Multidimensional Model had a functionality to exclude attribute values by default, even though they were not part of any selection. The result would be that records with this value or records with a relationship to this value would not be included in a selection, unless the attribute value would be selected explicitly.

For example you'd likely report about orders in most cases excluding a provisional order status. My aim is to prevent users from accidentally reporting results including such provisional orders.

My knowledge of Multidimensional is dated at least six years, I have no recollection of this existing in Tabular Model nor did my Google result give me anything useful. However, I would like to think this should be possible one way or another, even with a workaround.

Do you know how to accomplish this? If any relevant information is missing, please let me know.

Improve this question
2
  • Are you using Multidimensional or Tabular? These are two completely different systems. One is using MDX, the other is using DAX. Commented Apr 3, 2024 at 12:08
  • @RADO I'm using Tabular, though I seem to remember such a feature with MDX from years ago. That got me wondering for Tabular. Commented Apr 5, 2024 at 5:36

2 Answers 2

Reset to default
3

For example you'd likely report about orders in most cases excluding a provisional order status.

There's no modeling feature for this, except perhaps row-level security where you could prevent most users from seeing provisional orders.

Normally you would handle this at the reporting layer in Power BI by setting a report-level filter on order status.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for your response. That's too bad. I have quite a few 'advanced' users using pivot tables directly onto the model, so that's a bit tricky. Will think about options with row-level security or perhaps separated measures.
0

You can achieve something similar to hierarchical default values using ISFILTERED dax function. When is filtered is false, you will need to hardcode default value in each measure.

https://learn.microsoft.com/en-us/dax/isfiltered-function-dax

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.