topic Re: DAX to count # of non blank columns in a row in DAX Commands and Tips

DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 13:37:02 GMT

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

Re: DAX to count # of non blank columns in a row

Fowmy — Wed, 02 Sep 2020 13:58:08 GMT

Anonymous

Need more clarification on the LayersMax?
Provide more examples with explanations.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:05:42 GMT

Anonymous - Try:

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

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 14:11:13 GMT

Fowmy That column always refers to the last person in each row. So first row refers to John, second row refers to Mary, third refers to Andrew, and so goes.

When we look at Andrew, for example, we see he is present in 3 rows. So what is the max # of layers (non blank columns) among those 3 rows? The max is 4, so the Max column in 3rd row should give me 4.

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 14:10:46 GMT

Greg_Deckler that gave me 5 for all rows.

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:24:37 GMT

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

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:23:41 GMT

Anonymous - Maybe:

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

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 14:22:08 GMT

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

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:23:04 GMT

Anonymous - Put MAX in front of them, sorry, I'll update the post.

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 14:26:39 GMT

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 😞

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 14:34:44 GMT

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

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:39:49 GMT

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)

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 14:42:11 GMT

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

Re: DAX to count # of non blank columns in a row

amitchandak — Wed, 02 Sep 2020 14:42:19 GMT

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)

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 15:00:21 GMT

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

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 15:04:19 GMT

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.

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 15:10:48 GMT

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

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 15:52:55 GMT

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

Re: DAX to count # of non blank columns in a row

Greg_Deckler — Wed, 02 Sep 2020 15:59:37 GMT

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

Re: DAX to count # of non blank columns in a row

Anonymous — Wed, 02 Sep 2020 16:23:52 GMT

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?