Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a multi-tenant application, and I want to use the Hierarchy control as a slicer for choosing various levels in an organization. I'm following the instructions for creating the hierarchy control (PATH, PATHLENGTH, Level1, Level2, ...), but the issue I'm having is that the number of levels is different from one tenant to another.
For example, if each tenant is a retail company, the first one may be a Mom & Pop store with 1 location (1 organization and 1 level), and the next company might be a national retailer with 100 locations (100 organizations stratified in 10 levels). Other than providing 20 levels (hoping this will cover the needs of all tenants), how do I accommodate different hierarchy depths?
Manually creating the levels is easy when you have 1 customer, but I need an automated way of creating the levels. I know the max tree depth for each tenant, but I don't know how to add calculated columns based on the tree depth so that each tenant has what it needs and no more.
Unfortunately, I'm a newb at DAX. I keep thinking there's a way to do it, but I cannot find anything anywhere about how to do it. I hope it's possible, but I'm starting to doubt myself! ha!
Any guidance is greatly appreciated!
Cheers,
Chris
Hi @CVance ,
Build a standard model of hierarchical structure in the data model. If a tenant has no value at a certain level, use a null value instead.
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply, but how do I do that?
Here's the Dax for Level 3 of the hierarchy:
Level3 =
VAR LevelNumber = 3
VAR LevelKey = PATHITEM ( MyHierarchy[EntityPath], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( MyHierarchy[Name], MyHierarchy[ID], LevelKey )
VAR Temp = IF(ISBLANK(LevelName), NULL, LevelName)
VAR Result = Temp
RETURN
Result
This results in an error message: "Operator or expression 'NULL' is not supported in this context."
How else do I use NULL when there is no value in the row at the hierarchy level?
Thanks!
Chris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |
User | Count |
---|---|
24 | |
3 | |
2 | |
2 | |
2 |