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

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

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 

 

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.

 

 

 

 

Highlighted

Awesome, will do!


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

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

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!




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