DAX measure that ignores part of the filter

[Michal_cwiok]

Hi,

One calculated measure is giving me a hard time and I wanted to consult with the community. As my client requested, the dashboard should enable users to see their results only (a rather simple RLS solution), but also aggregated results of other users but only for products in the same class as the first user. A good example would be with retail suppliers and sales (products are in a hierarchy: class -> subclass -> SKU):

Sales Details			Market Details
SKU	Sales		Products hierarchy	Sales
Supplier1 SKU1	125		Class 1	840
Supplier1 SKU2	124		Subclass1.1	587
Supplier1 SKU3	106		Subclass1.2	253
Supplier1 SKU4	110		Class2	1061
Supplier1 SKU5	127		Subclass2.1	604
Supplier1 SKU6	102		Subclass2.2	457

Is it possible to create such a DAX query that takes into account the classes and subclasses of the Supplier1 and shows sales results for all of the supplier for those classes and subclasses?

I was trying to use query like below, but without success:

Sales_subclass := CALCULATE(Sales;ALL(Supplier[SupplierName]);FILTERS(Products[Subclass]))

Which (as I see it) takes the existing filters from the subclass column and applies them to the data ignoring the filter for supplier. Unfortunatelly this does not work and still applies supplier as a filter.

Would appreciate any help, thank you.

[Eric_Zhang · Accepted solution]

---

@Michal_cwiok wrote:

Hi,

One calculated measure is giving me a hard time and I wanted to consult with the community. As my client requested, the dashboard should enable users to see their results only (a rather simple RLS solution), but also aggregated results of other users but only for products in the same class as the first user. A good example would be with retail suppliers and sales (products are in a hierarchy: class -> subclass -> SKU):

Sales Details			Market Details
SKU	Sales		Products hierarchy	Sales
Supplier1 SKU1	125		Class 1	840
Supplier1 SKU2	124		Subclass1.1	587
Supplier1 SKU3	106		Subclass1.2	253
Supplier1 SKU4	110		Class2	1061
Supplier1 SKU5	127		Subclass2.1	604
Supplier1 SKU6	102		Subclass2.2	457

Is it possible to create such a DAX query that takes into account the classes and subclasses of the Supplier1 and shows sales results for all of the supplier for those classes and subclasses?

I was trying to use query like below, but without success:

Sales_subclass := CALCULATE(Sales;ALL(Supplier[SupplierName]);FILTERS(Products[Subclass]))

Which (as I see it) takes the existing filters from the subclass column and applies them to the data ignoring the filter for supplier. Unfortunatelly this does not work and still applies supplier as a filter.

Would appreciate any help, thank you.

---

@Michal_cwiok

I don't think a measure could do that, RLS is applied to dataset(the data you don't have access is missing in tables). You may implment that by re-modeling your data, eg, create an aggregated calculated table 

aggreagated Table =
SUMMARIZE (
    'Source table',
    'Source table'[subclass],
    "sales in the subclass", SUM ( 'Source table'[sales] )
)

Before RLS applied.

When RLS is applied for user1.

See the attached demo.

[Michal_cwiok]

I have also tried this approach:

CALCULATE(CALCULATE(Sales[Sales];ALL(Supplier[SupplierName]));CALCULATETABLE('Product';FILTERS('Products'[SubClass]))) 

but also without success.

Thank you