cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
evolu6ion Frequent Visitor
Frequent Visitor

Get total from Dimension tables

Hello,

 

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

 

Here is my dummy data:

2019-04-09 12_20_54-Untitled - Power BI Desktop.pngData 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).

 

2019-04-09 10_55_59-Untitled - Power BI Desktop.png

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:

FacilityIDNb of product categoriesTotal categories
2113
2222
2313
2411
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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Get total from Dimension tables

@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
)

View solution in original post

evolu6ion Frequent Visitor
Frequent Visitor

Re: Get total from Dimension tables

@natelpeterson  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.

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Get total from Dimension tables

@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 Frequent Visitor
Frequent Visitor

Re: Get total from Dimension tables

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

2019-04-09 14_44_39-task11 calculations - Power BI Desktop.png

 

Here is the desired result:

FacilityIDNb of product categoriesTotal categories
2113
2222
2313
2411
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.

Super User
Super User

Re: Get total from Dimension tables

@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
)

View solution in original post

evolu6ion Frequent Visitor
Frequent Visitor

Re: Get total from Dimension tables

@natelpeterson  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.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)