Get total from Dimension tables

[evolu6ion]

Hello,

I am facing an issue regarding calculating the total from Dimension tables using specific constraints.

Here is my dummy data:

Data model

The only difference with the above data model and my actual data is that the relation highlighted in a one-to-many (Facility Levels 1---* Facilities).

In red, the goal is to calculate the total number of categories available for a facility, with the constraint of not counting for categories with higher level (1 being the highest) than the Facility Level.

Currently, I have the right calculation for measure "Nb of product categories":

Nb of product categories = 
CALCULATE(
    DISTINCTCOUNT(Products[Category]);
    FILTER(
        Surveys;
        RELATED('Category Levels'[Level]) >= RELATED('Facility Levels'[Level])
    )
)

But I am struggling to get the "Total categories" measure.

For example, FacilityID 22 is a level 2 facility. Which means its product basket can only account for products with category levels being >= 2. So the ProductID 12 and ProductID13 are correctly computed in the "Nb of product categories" measure; they are Level 2 (ProductID 12) and Level 3 (ProductID13) category. Same for ProductID 15 (Category Level 1) that is not counted, which is the goal.

Now for the "Total categories" measure, row FacilityID 22 in the table on the right (with the red rectangle) should only compute 2 as only 2 of the products categories are within the facility Level constraint.

Here is the desired result:

FacilityID	Nb of product categories	Total categories
21	1	3
22	2	2
23	1	3
24	1	1
25		0

I choose to use a snowflake schema because in my business case, the users might add different Category Levels and Facility Levels depending on countries specificities. With this model, it looks like it would be easier to maintain.

Your help will be greatly appreciated. Thanks in advance.

Best regards,

N.

[Anonymous · Accepted solution]

@evolu6ion - Would something like this work?:

Total product categories = 
var fac_level = MAX('Facility Levels'[Level])
return 
CALCULATE(
    DISTINCTCOUNT(Products[Category]),
    RELATED('Category Levels'[Level]) <= fac_level
)

[Anonymous]

@evolu6ion -

I won't claim that it's an optimal solution, but one way to do it would be to modify:

RELATED('Category Levels'[Level]) >= RELATED('Facility Levels'[Level])

with 

RELATED('Category Levels'[Level]) >= 0 --Small number that will guarantee it doesn't get filtered out

[evolu6ion]

@Anonymous  Thanks for your answer. Unfortunately, it doesn't seem to solve the problem.

Here is the desired result:

FacilityID	Nb of product categories	Total categories
21	1	3
22	2	2
23	1	3
24	1	1
25		0

FacilityID 21 and 23 are Level 1 facilities ==> they should survey all product category.

FacilityID 22 is Level 2 facility ==> it should only survey product category Level 2 and below.

FacilityID 24 is Level 3 facility ==> it should only survey product category Level 3 and below.

FacilityID 25 is Level 4 facility ==> it should only survey product category Level 4 and below. (Currently no product are in this category Level).

Thanks for your help.

N.

[Anonymous · Accepted solution]

@evolu6ion - Would something like this work?:

Total product categories = 
var fac_level = MAX('Facility Levels'[Level])
return 
CALCULATE(
    DISTINCTCOUNT(Products[Category]),
    RELATED('Category Levels'[Level]) <= fac_level
)

[evolu6ion · Accepted solution]

@Anonymous  Thanks a lot! I've modified a little bit your last measure to get to the desired result. And it works on my real data!

Here is the final measure:

Total product categories = 
var fac_level = MAX('Facility Levels'[Level])
return 
CALCULATE(
    DISTINCTCOUNT(Products[Category]);
    FILTER(
        ALLSELECTED(Products);
        RELATED('Category Levels'[Level]) >= fac_level
    )
)

In order for this to work, the cross filter direction between Facilities and Facility Levels actually has to be bidirectionnal.

Once again, thank you very much!

Best regards,

N.