Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey everyone.
H1 | H2 | H3 | H4 | H5 | # Layers | Max |
John Smith | 1 | 5 | ||||
John Smith | Mary Jane | 2 | 5 | |||
John Smith | Mary Jane | Andrew Johnson | 3 | 4 | ||
John Smith | Mary Jane | Andrew Johnson | Ariel Parker | 4 | 4 | |
John Smith | Mary Jane | Andrew Johnson | Mark Williams | 4 | 4 | |
John Smith | Mary Jane | Jackson Mills | 3 | 5 | ||
John Smith | Mary Jane | Jackson Mills | Justin Hunt | 4 | 5 | |
John Smith | Mary Jane | Jackson Mills | Justin Hunt | Janet Lind | 5 | 5 |
This is the organizational structure table that I have. I need the # of Layers column to count the number of non blank columns in that row. And I need the Max column to give me the max count of layers for the person in the very right.
So, for example, in row number 3, there are only 3 columns, but Andrew is present in a structure that has 4 layers, so the max for him should give me 4.
I need DAX measures for both columns.
Thanks
Solved! Go to Solution.
@Anonymous - OK, I actually tested this one, PBIX is attached below sig, Table (12), Page 12, still working on Max.
# Layers =
VAR __H1 = FILTER({ MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) },[Value] <> "")
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
RETURN
PATHLENGTH(__Hierarchy)
@Anonymous , Try a new column like
new column =
var _num = if(isblank([H1]),1,0) + if(isblank([H2]),1,0) + if(isblank([H3]),1,0)+ if(isblank([H4]),1,0)+ if(isblank([H5]),1,0)
var _H1 = maxx(filter(table[H1] =earlier([H1])),_num)
var _H2 = if(isblank(table[H2]) , 99, maxx(filter(table[H2] =earlier([H2])),_num))
var _H3 = if(isblank(table[H3]) , 99, maxx(filter(table[H3] =earlier([H3])),_num))
var _H4 = if(isblank(table[H4]) , 99, maxx(filter(table[H4] =earlier([H4])),_num))
var _H5 = if(isblank(table[H5]) , 99, maxx(filter(table[H5] =earlier([H5])),_num))
return
min(min(min(min(_H1,_H2),_H3),_H4),_H5)
@Anonymous -
Max =
VAR __H1 = { MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) }
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
VAR __Length = PATHLENGTH(__Hierarchy)
VAR __Item = PATHITEM(__Hierarchy, __Level)
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
ALL('Table'),
"__Hierarchy",
CONCATENATEX({[H1],[H2],[H3],[H4],[H5]},[Value],"|") // you don't need MAX here row context
),
"Length",PATHLENGTH([__Hierarchy])
"Found",IF(FIND(__Item,[__Hierarchy],,0)<>0,1,0)
)
RETURN
MAXX(FILTER(__Table,[Found]=1),[Length])
Max = VAR __H1 = { MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) } VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|") VAR __Length = PATHLENGTH(__Hierarchy) VAR __Item = PATHITEM(__Hierarchy, __Level) VAR __Table = ADDCOLUMNS( ADDCOLUMNS( ALL('Table'), "__Hierarchy", CONCATENATEX({[H1],[H2],[H3],[H4],[H5]},[Value],"|") // you don't need MAX here row context ), "Length",PATHLENGTH([__Hierarchy]) "Found",IF(FIND(__Item,[__Hierarchy],,0)<>0,1,0) ) RETURN MAXX(FILTER(__Table,[Found]=1),[Length])
1) That _Level part is underlined, saying Cannot find name
2) The CONCATENATEX part, it won't let me use the columns.
3) That whole line that begins with "Found" is underlined
@Anonymous - And here is Max, updated PBIX with both attached below sig
Max =
VAR __H1 = FILTER({ MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) },[Value] <> "")
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
VAR __Length = PATHLENGTH(__Hierarchy)
VAR __Item = PATHITEM(__Hierarchy, __Length)
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
ALL('Table (12)'),
"__Hierarchy",
CONCATENATEX(FILTER({ [H1],[H2],[H3],[H4],[H5]},[Value]<>""),[Value],"|")
),
"Length",PATHLENGTH([__Hierarchy]),
"Found",IF(FIND(__Item,[__Hierarchy],,0)<>0,1,0)
)
RETURN
MAXX(FILTER(__Table,[Found]=1),[Length])
@Greg_Deckler Layers column worked!
Trying the Max one but it's taking forever to load... my database is pretty extense and it's live connected
@Anonymous - I will investigate how I might be able to optimize it. But yes, since you have to test every single other row then it will take a while. Might be able to optimize it though, I have some ideas.
@Anonymous - Try this version:
Max =
VAR __H1 = FILTER({ MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) },[Value] <> "")
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
VAR __Length = PATHLENGTH(__Hierarchy)
VAR __Item = PATHITEM(__Hierarchy, __Length)
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
FILTER(ALL('Table (12)'),[H1]=__Item||[H2]=__Item||[H3]=__Item||[H4]=__Item||[H5]=__Item),
"__Hierarchy",
CONCATENATEX(FILTER({ [H1],[H2],[H3],[H4],[H5]},[Value]<>""),[Value],"|")
),
"Length",PATHLENGTH([__Hierarchy]),
"Found",IF(FIND(__Item,[__Hierarchy],,0)<>0,1,0)
)
RETURN
MAXX(FILTER(__Table,[Found]=1),[Length])
"Couldn't load data for this visual"
If you run out of ideas, maybe I should add that as a calculated column in the database instead of creating a measure on Power BI
@Anonymous - It would probably be better as a calculated column and then if you do that, you might want a Power Query solution instead. How many rows are we talking about? I want to mock up some data that I can test with unless you can share the dataset. There may yet be some optimizations to perform. I have some blog articles on DAX Performance Tuning:
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
One last question:
I added a column next to the layers column called "Flag Layers" which basically is
@Anonymous - Is that a measure?
Also, how many rows are we talking about? I'd really like to test this at the scale that you have. I can generate the data myself pretty easily.
About 5000 rows. I can't share the dataset.
If you can test another Max measure that might work for 5000 rows, that would be great.
@Anonymous - I was able to get this functioning with 5000 rows pretty well. Updated PBIX is attached, Sheet1 table, Sheet1 page.
Max 1 =
VAR __H1 = FILTER({ MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) },[Value] <> "")
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
VAR __Length = PATHLENGTH(__Hierarchy)
VAR __Item = PATHITEM(__Hierarchy, __Length)
VAR __Table =
ADDCOLUMNS(
ADDCOLUMNS(
FILTER(ALL('Table (12)'),[H1]=__Item||[H2]=__Item||[H3]=__Item||[H4]=__Item||[H5]=__Item),
"__Hierarchy",
CONCATENATEX(FILTER({ [H1],[H2],[H3],[H4],[H5]},[Value]<>""),[Value],"|")
),
"Length",PATHLENGTH([__Hierarchy]),
"Found",IF(FIND(__Item,[__Hierarchy],,0)<>0,1,0)
)
RETURN
MAXX(FILTER(__Table,[Found]=1),[Length])
Awesome, will do!
@Anonymous - Try:
# Layers =
VAR __Hierarchy = MAX([H1]) & "|" & MAX([H2]) & "|" & MAX([H3]) & "|" & MAX([H4]) & "|" & MAX([H5])
RETURN
PATHLENGTH(__Hierarchy)
@Anonymous - Maybe:
# Layers =
VAR __H1 = { MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) }
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
RETURN
PATHLENGTH(__Hierarchy)
# Layers = VAR __H1 = { MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) } VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|") RETURN PATHLENGTH(__Hierarchy)
Keeps giving me 5 for all of them 😞
@Anonymous - OK, I actually tested this one, PBIX is attached below sig, Table (12), Page 12, still working on Max.
# Layers =
VAR __H1 = FILTER({ MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) },[Value] <> "")
VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
RETURN
PATHLENGTH(__Hierarchy)
# Layers = VAR __H1 = { [H1], [H2], [H3], [H4], [H5] } VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|") RETURN PATHLENGTH(__Hierarchy)
For those [H1], [H2], etc it won't let me use the columns. Only lets me choose from tables or measures
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |