topic Re: NEED HELP IN POWER BI in DAX Commands and Tips

NEED HELP IN POWER BI

sarath_chandra — Sat, 22 Jul 2023 15:51:20 GMT

Hi power bi developers, please help me out
I have a columns like this in power bi - attachment given.

1.I have to keep a filter with higher members- only (XX123 ,XX124)- IF I SELECT THE ANY ONE IN FILTER IT SHOULD SHOW FROM HIGHER TO LOWER path with aggregation chart

I have to show the chart like this - chart snap attached below. OR ANY RECOMMENDED VISUALS IN POWER BI.

PLEASE HELP ME ! i'M NEW TO POWER BI🙏

Re: NEED HELP IN POWER BI

sarath_chandra — Sat, 22 Jul 2023 15:53:01 GMT

tamerj1 Jihwan_Kim Martin_D Sahir_Maharaj please help me out .please

Re: NEED HELP IN POWER BI

Martin_D — Sat, 22 Jul 2023 17:03:47 GMT

Hi sarath_chandra ,

You can add the visual you need from App Source. In Power BI Desktop click on "Get more visuals" and search for one of:

Hierarchy Chart by MAQ Software
swOrgChart
Hierarchy Chart by Akvelon
TreeViz
Ultimate Decomposition Tree

While with Hierarchy Chart by MAQ Software, swOrgChart, and Hierarchy Chart by Akvelon you can use your PK and CK columns as they are, with TreeViz and Ultimate Decomposition Tree you need to create columns per category (e.g. manager, team lead, implementer, ...) first in your table.

For each visual you can download a sample file so you can choose your preferred option.

Martin

Re: NEED HELP IN POWER BI

sarath_chandra — Sun, 23 Jul 2023 06:11:36 GMT

Thanks for input Martin_D
but im expecting like this as mentioned below
im having parent and child columns are duplicates and im having the level in another column

im need this kind of desired output

please help me in this ...

Re: NEED HELP IN POWER BI

sarath_chandra — Sun, 23 Jul 2023 06:12:54 GMT

Jihwan_Kim please help me out in above problem

Re: NEED HELP IN POWER BI

Martin_D — Mon, 24 Jul 2023 00:07:28 GMT

Referring to your table in message 1, in order to build the slicer you need:

A disconnected table (i.e. a table that has no relationship in the data model) with a distinct list of your parent nodes. This DAX code creates this table:
DAX code for a new table:
Slicer Values = SUMMARIZECOLUMNS ( 'Table'[PK] )
A measure to be used to select the complete branch below the top node that you want to select in the slicer:
DAX code for a new measure:
Show Node =
VAR _TopNode = SELECTEDVALUE ('Slicer Values'[PK] )
VAR _CurrentNodePath = SELECTEDVALUE ( 'Table'[Path] )
RETURN
IF (
OR (
NOT ( ISFILTERED ( 'Slicer Values' ) ),
PATHCONTAINS( _CurrentNodePath, _TopNode )
),
"show"
)
Apply the new measure as a filter to your preferred visual for showing the hierarchy like this:

With Hierarchy Chart by MAQ Software as an example, the solution bahaves like this:

No parent node selected in the slicer:

A parent node selcted:

A different parent node selected:

Re: NEED HELP IN POWER BI

sarath_chandra — Sun, 23 Jul 2023 11:27:47 GMT

Thanks for your great work! Martin_D .
above code - if i filter it will show upto the hier length of (1,2,3) not filtering the 4 and 5 hier depths

can you please help me in second case with multiple parent and multiple child column values?

but actually i need that in matrix table instead of hier chart

please help me out ! .Thanks for your great work ! great appreciation

Re: NEED HELP IN POWER BI

Martin_D — Mon, 24 Jul 2023 00:06:28 GMT

Hi sarath_chandra ,
Referring to your requirements "i need that in matrix table" and "multiple parent and multiple child column values", with this being the full hierarchy as an example

and this being the slicer selection as an example

what would be your expected result that you want to see in the matrix table: Version A, version B, version C, or something different (please draw)?

Re: NEED HELP IN POWER BI

sarath_chandra — Mon, 24 Jul 2023 02:27:28 GMT

Thank you so much Martin_D -really appreciable .Kudos
Im attaching the clear requirement here. i want single select slicer in PK like below .

the desired output in matrix table like below

please help me out Martin_D -Thanks for your great work - No words to describe!

Re: NEED HELP IN POWER BI

Martin_D — Mon, 24 Jul 2023 05:51:35 GMT

Hi sarath_chandra ,

OK, you only want single select, that's clear now. But you are still not getting to the point: Your 2 examples in message 8 do only show what should happen if a root node is selected - XX123 and XX124 are root nodes according to your table in message 1 (root node = node without parents). Are these example in message 8 to be interpreted as you only want to allow for single select of a root node, no other nodes in the slicer? Please be specific whether you only want to allow for filtering root nodes, or otherwise please show specific examples what you want to see if a non-root node is selected.

Re: NEED HELP IN POWER BI

sarath_chandra — Mon, 24 Jul 2023 06:33:54 GMT

Thanks a lot Martin_D .
Actually don' t consider the message 1

My clear requirement is given in message 8
Always the slicer selection is single (that is PK column) specfically in message 8 (level : -1 is top level) and then 0,1,2,3,4,5... are lower levels respestively

Level: -1 is only going to be in slicer selection no other levels .

please view my exact matrix table with levels - how i want in power bi

If i select the slicer (XX123) it should show the full levels from -1(top level) to lower level (3) in matrix table
Hope I answered !
thank you so much for your great work Martin_D

If you have any question .please let me know!

Re: NEED HELP IN POWER BI

sarath_chandra — Mon, 24 Jul 2023 15:03:41 GMT

Martin_D Thanks for your work!.Is there is a way to do this,above problem in Power BI?🙏🙏
save me from this task please!

Re: NEED HELP IN POWER BI

Martin_D — Thu, 27 Jul 2023 17:25:33 GMT

sarath_chandra This way it's a pretty basic task in Power BI. You need to do the following:

Expand your PK and CK columns to one column per hierarchy level. This code does the job (for up 6 levels depth):
Code for DAX calculated table:
Simple Expanded Hierarchy Table =
ADDCOLUMNS (
SUMMARIZECOLUMNS (
'Table'[CK],
'Table'[PK],
'Table'[Path]
),
"Level 1",
PATHITEM ( [Path], 1, TEXT ),
"Level 2",
PATHITEM ( [Path], 2, TEXT ),
"Level 3",
PATHITEM ( [Path], 3, TEXT ),
"Level 4",
PATHITEM ( [Path], 4, TEXT ),
"Level 5",
PATHITEM ( [Path], 5, TEXT ),
"Level 6",
PATHITEM ( [Path], 6, TEXT ),
"Pathlength",
PATHLENGTH ( [Path] )
)
Create the matrix visual and add the required level columns to the Rows of the visual (Level 1 - Level 5 with your sample data).
Right-Click on a row in the matrix visual and click on Expand > All. This wíll show the complete hierarchy but will also show blank rows if a node has no child.
In order to hide the blank rows, create a measure that returns blank for these blank rows (you can build your custom measures out of this, returning anything else you need instead of the level number). Code for up to 6 levels.
Code for a measure:
Level =
SWITCH (
TRUE (),
ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 6] ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 6] ) > 0, 6 - 2 ),
AND ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 5] ), NOT ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 6] ) ) ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 5]) > 0, 5 - 2 ),
AND ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 4] ), NOT ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 5] ) ) ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 4]) > 0, 4 - 2 ),
AND ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 3] ), NOT ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 4] ) ) ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 3]) > 0, 3 - 2 ),
AND ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 2] ), NOT ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 3] ) ) ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 2]) > 0, 2 - 2 ),
AND ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 1] ), NOT ( ISINSCOPE ( 'Simple Expanded Hierarchy Table'[Level 2] ) ) ),
IF ( COUNTA ( 'Simple Expanded Hierarchy Table'[Level 1]) > 0, 1 - 2 ),
BLANK ()
)
Add the new measure to the Values of the matrix visual.
In order to filter the hierarchy as specified, add a slicer to the page and add the Level 1 column to the slicer.

Further reading on implementing parent-child hierarchies in Power BI: https://www.daxpatterns.com/parent-child-hierarchies/

Re: NEED HELP IN POWER BI

sarath_chandra — Wed, 02 Aug 2023 16:58:31 GMT

Martin_D Thanks a lot for your work . Very Sorry for delayed response .
actually small change in requirement .

Before requirement was unique value of PK column as slicer/filter for hier levels - but now - I have to bring all the parent key into slicer --> if i select the any one of the parent key - it should show all the child key (how to create filter/slicer setup).

for example : if i select the level 2 as first parent key in slicer - it should show all the child keys (level 3,level4,level 5 and so on..)

here if i select the 6T456U in slicer it should start from 6T456U --> then It show the child key WE456L (level 2) and also (level 3) 45T78KU ...

please help me out !!Thanks for your valuable time to let me know!.very grateful for your help

Re: NEED HELP IN POWER BI

sarath_chandra — Thu, 03 Aug 2023 14:32:32 GMT

Martin_D -please help me out on this task!!

Re: NEED HELP IN POWER BI

Martin_D — Sat, 05 Aug 2023 10:55:54 GMT

Hi sarath_chandra ,

I'd recommend that you consult your business stakeholders to choose a solution that reasonably balances between business requirements and implementation effort and that you can maintain on your own. A simple solution for your use case would be to just add additional slicers for each level of the hierarchy. In order to not show child-only nodes in the slicers you can set up visual level filters to the slicers based on the path length column.

Martin

Re: NEED HELP IN POWER BI

sarath_chandra — Sat, 12 Aug 2023 07:34:02 GMT

let me know this is task feasible/possible in power bi or not please Martin_D

please help me out with your solution

input file structure:

output requirement:

Re: NEED HELP IN POWER BI

Martin_D — Sat, 12 Aug 2023 15:46:40 GMT

Hi sarath_chandra ,

Yes, this is feasible in Power BI, but it does not very well utilize the basic functionality of Power BI, thus it's higher implementation effort. Actually that is what you see in message 7, version B.

Way easier to implement would be a solution that even when selecting a node that is not a top level node, the corresponding top level nodes are still shown in the table, but only with the selected child node, not with all its children. That's the solution described in message 15.

As a project manager being responsible for this project, I would not take the risk to deliver a solution that nobody in my team can maintain later on in case urgent change request are coming in, and the solution will require maintenance, e.g. if a new level in the hierarchy gets introduced.

If you still want to build it, here is what you need to do on a conceptual level: Every node that exists on any level in the original hierachy and shall appear in the filter needs to exist in the level 1 column of the table created in message 12, step 1, having its subtree in the level 2, 3, 4,... columns. Technically you extend the hierarchy table by creating a union of tables that contain your hierachy, each time removing one more level from the top, until the lowest level that shall appear in the slicer is on level 1. If you want to show measures in the table as well that relate to the node appearing in the table after filtering, then you also need to extend the measure described in message 12, step 4 accordingly. With this solution, you need only one slicer for all levels and you put your level 1 column of the extended hierarchy column into the slicer.

Martin