Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

23 REPLIES 23
amitchandak
Super User
Super User

@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
Super User
Super User

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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

@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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

 

 

 

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Awesome, will do!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@Anonymous - Try:

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler  that gave me 5 for all rows. 

@Anonymous - Maybe:

 

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors