Data granularity filter issue

[anandav]

Hi All,

I have the following data model

The data is displayed in a table visual as:

There are many other dim tables connecting to Sales but this will do to explain my requirement.

Since the Budget is set at per Site per Product Sub-Category, the Budget Amount should NOT be displayed if  the data is filtered by any other dim attributes other than Site or Product Sub-Category.

In a Table or Matrix visual, I can achieve this by using INSCOPE() function.

Problems

1] When the Table visual also has Site ID and Product ID, the Budget Amount displayed is for Product Sub-Category that the product belongs to.

I want this to be 0.

2] When the table is filtered using Product ID Filter (in Filter Pane), the Budget Amount displayed is for Product Sub-Category that the product belongs to.

I want to display 0 if the table is filtered by any other dim attributes other than Site, Product Category or Product Sub-Category.

3] When I want to display the budget in a Card visual, since there is no context/scope, the Budget Amount is not displayed as the measure is checking for INSCOPE.

The above are all correct behavior according to  the Table visual and teh measure. I understand that.

But it causes confusion for someone who does not know the granularity of the budget.

I read on ISFILTERED and HASONEVALUE but these cannot be used as there many variation of dim tables and attributes and too many to list explicitly in functions. I also could not find a way to check whether a filter is in default state (‘All' in Filter Panel).

Any suggestion on how this can be solved?

I am happy to change the data model.

Link to pbix file: https://1drv.ms/u/s!AgWALQ1qUAnPkFOwvnbDxmhpmsDp?e=DTZPAV 

[v-janeyg-msft]

Hi, @anandav 

It's my pleasure to answer for you.

According to your description, I create a measure instead of 'budget amount' in the visual.

Like this:

Measure =
IF (
    ISFILTERED ( dimProducts[Product ID] ) && ISFILTERED ( dimStores[Store ID] ),
    0,
    SUM ( Budget[Budget Amount] )
)

If it doesn't solve your problem,please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[anandav]

Hi @v-janeyg-msft ,

Sorry for the late reply.. some personal issues delayed me.

Thank you for the reply Janey. I tried your measure but when I select a product, it still filters the Budget for that product's Product SubCategory.

What I am looking for is a way to not display the budget value :

1. Uness Site or Product SubCategory is selected for filtering

2. Other dims or attributes are selected for filtering which is not Site or Product Category

Hope I am clear in explaining the requirement.

Thanks,

Anand

[v-janeyg-msft]

Hi, @anandav 

If you don't want to let the measure be affected by any filter.

Try like this:

Measure 2 = SUMX(ALL(Budget),[Budget Amount])

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.