How to create Organization hierarchy with Direct Query

[admin_xlsior]

Hi guys,

How to create Organization Hierarchy if I'm using Direct Query. As I know we used to have PATH, PATHLENGTH ,etc but this function is not supported in Direct Query.

If lets say I have this kind of data:

Category	Parent category	Parent category code
CORP PROCUREMENT CATEGORIES	null	null
ADVERTISING	CORP PROCUREMENT CATEGORIES	44000000
COMMISSIONS AND REBATES	CORP PROCUREMENT CATEGORIES	44000000
OFFICE AND DESK ACCESSORIES	CORP PROCUREMENT CATEGORIES	44000000
OFFICE MACHINES	CORP PROCUREMENT CATEGORIES	44000000
PAYROLL	CORP PROCUREMENT CATEGORIES	44000000
UTILITIES	CORP PROCUREMENT CATEGORIES	44000000
VEHICLES	CORP PROCUREMENT CATEGORIES	44000000
WORKPLACE SERVICES	CORP PROCUREMENT CATEGORIES	44000000
Calculators or accessories	OFFICE MACHINES	44100000
Computers	OFFICE MACHINES	44100000
Printer and facsimile and photocopier supplies	OFFICE MACHINES	44100000
Toner	OFFICE MACHINES	44100000
Highlighters	OFFICE AND DESK ACCESSORIES	44110000
Pen or pencil sets	OFFICE AND DESK ACCESSORIES	44110000
Scissors	OFFICE AND DESK ACCESSORIES	44110000
Standard envelopes	OFFICE AND DESK ACCESSORIES	44110000
Staplers	OFFICE AND DESK ACCESSORIES	44110000
Staples	OFFICE AND DESK ACCESSORIES	44110000
Cars	VEHICLES	Vehicles
Tractors	VEHICLES	Vehicles
Cleaning	WORKPLACE SERVICES
Training	WORKPLACE SERVICES

Thanks,

[admin_xlsior]

Hi Li,

I believe when you said it is not possible is because it's lack of CategoryCode whereby I have ParentCategoryCode ? Understand this, only I thought we can use the name instead. 

However I just add a Category code so I think it will be easier. Like this :

CATEGORYCODE	CATEGORYNAME	PARENTPRODUCTCATEGORYCODE	PARENTPRODUCTCATEGORYNAME
44000000	CORP PROCUREMENT CATEGORIES	NULL	NULL
44100000	OFFICE MACHINES	44000000	CORP PROCUREMENT CATEGORIES
44101501	Photocopiers	44100000	OFFICE MACHINES
44101603	Paper shredding machines or accessories	44100000	OFFICE MACHINES
44101801	Calculators or accessories	44100000	OFFICE MACHINES
44103100	Printer and facsimile and photocopier supplies	44100000	OFFICE MACHINES
44103103	Toner	44100000	OFFICE MACHINES
44110000	OFFICE AND DESK ACCESSORIES	44000000	CORP PROCUREMENT CATEGORIES
44121506	Standard envelopes	44110000	OFFICE AND DESK ACCESSORIES
44121615	Staplers	44110000	OFFICE AND DESK ACCESSORIES
44121618	Scissors	44110000	OFFICE AND DESK ACCESSORIES
44121702	Pen or pencil sets	44110000	OFFICE AND DESK ACCESSORIES
44121716	Highlighters	44110000	OFFICE AND DESK ACCESSORIES
44122107	Staples	44110000	OFFICE AND DESK ACCESSORIES
PAYROLL	PAYROLL	44000000	CORP PROCUREMENT CATEGORIES
Computers	Computers	44100000	OFFICE MACHINES
Vehicles	VEHICLES	44000000	CORP PROCUREMENT CATEGORIES
Cars	Cars	Vehicles	VEHICLES

But the main problem is actually not the column, but I cannot use those DAX function since it is not supported in Direct Query. Or is that any trick so we can still use it ?

The visual / outcome however, can be vary. First is of couse the category tree it self. I will put relationship also for that Category code to my item table so it will have the items listed inside of each category.

Maybe also I need to separate the leveling, so 1st level - 2nd and 3rd probably will shows its own visual to just display how many item count in that level. 

Thanks

[v-yingjl]

Hi @admin_xlsior ,

You can create a measure using Lookupvalue() to achieve your requirement under Direct query mode:

PARENTPRODUCTCATEGORYNAME = 
LOOKUPVALUE (
    'Hierarchy'[Category name],
    'Hierarchy'[Category code], SELECTEDVALUE ( 'Hierarchy'[Parent product category code] )
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[admin_xlsior]

Hi,

Thanks but how to identify the certain row is Level 1 / 2 / 3 ?

Thanks,

[v-yingjl]

Hi @admin_xlsior ,

Since Direct Query mode do not support parent-and-child functions so I'm afraid that currently there is not a good way to certain the row level 1/2/3... . Lookupvalue() is an alternative way to get the output.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @admin_xlsior ,

As your mentioned, when in DirectQuery mode, it's not possible to use the family of DAX PATH() functions that generally handle Parent-Child structures.

Based on your sample data, what is your expected output? Maybe you can share more details about this issue for further discussion.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.