DAX to count # of non blank columns in a row

[Anonymous]

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

[amitchandak]

@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)

[Greg_Deckler]

@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])
 

[Anonymous]

@Greg_Deckler 

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

[Greg_Deckler]

@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])

[Anonymous]

@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

[Greg_Deckler]

@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.

[Greg_Deckler]

@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])

[Anonymous]

@Greg_Deckler 

"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

[Greg_Deckler]

@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

[Anonymous]

@Greg_Deckler 

One last question:

I added a column next to the layers column called "Flag Layers" which basically is 

IF([Layers]>4,"ATTENTION","OK")

Once I added that column to my table, the whole Layers column went blank. Why?

[Greg_Deckler]

@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.

[Anonymous]

@Greg_Deckler 

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.

[Greg_Deckler]

@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])

[Greg_Deckler]

Awesome, will do!

[Greg_Deckler]

@Anonymous - Try:

# Layers =
  VAR __Hierarchy = MAX([H1]) & "|" & MAX([H2]) & "|" & MAX([H3]) & "|" & MAX([H4]) & "|" & MAX([H5])
RETURN
  PATHLENGTH(__Hierarchy)

[Anonymous]

@Greg_Deckler  that gave me 5 for all rows. 

[Greg_Deckler]

@Anonymous - Maybe:

# Layers =
  VAR __H1 = { MAX([H1]), MAX([H2]), MAX([H3]), MAX([H4]), MAX([H5]) }
  VAR __Hierarchy = CONCATENATEX(__H1,[Value],"|")
RETURN
  PATHLENGTH(__Hierarchy)

[Anonymous]

@Greg_Deckler 

# 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 😞 

[Greg_Deckler · Accepted 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]

@Greg_Deckler  

# 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