Forum Discussion
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 )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.
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
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.
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 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.