cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

DAX to count # of non blank columns in a row

Hey everyone.

 

H1H2H3H4H5# LayersMax
John Smith    15
John SmithMary Jane   25
John SmithMary JaneAndrew Johnson  34
John SmithMary JaneAndrew JohnsonAriel Parker 44
John SmithMary JaneAndrew JohnsonMark Williams 44
John SmithMary JaneJackson Mills  35
John SmithMary JaneJackson MillsJustin Hunt 45
John SmithMary JaneJackson MillsJustin HuntJanet Lind55

 

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

23 REPLIES 23
Highlighted

@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

Highlighted

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

@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

Highlighted

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


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

@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

Highlighted

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors