topic Re: DAX Span of control in DAX Commands and Tips

DAX Span of control

Anonymous — Thu, 03 Sep 2020 11:19:57 GMT

I have a table that looks like this

H1	H2	H3	H4	H5	Span of control
John					2
John	Andrew				1
John	Andrew	Mary			2
John	Andrew	Mary	James		2
John	Andrew	Mary	James	Josh	0
John	Andrew	Mary	James	Heather	0
John	Andrew	Mary	Justin		2
John	Andrew	Mary	Justin	Janet	0
John	Andrew	Mary	Justin	Michelle	0
John	Mark	Adam			1
John	Mark	Adam	Peter		0
John	Mark	Angela			1
John	Mark	Angela	Andrea		0

I need a measure that gives me that span of control in the last column. It basically gives me how many people directly report to the person on the very right. So first row refers to John, and John has Andrew and Mark reporting to them, so 2. Second row refers to Andrew, who has only Mary reporting to him, so 1. And so goes.

Thanks

Re: DAX Span of control

Greg_Deckler — Thu, 03 Sep 2020 11:25:52 GMT

Anonymous - Is it OK to unpivot your columns and remove the blanks and then work with that data model? I think it would be easier to achieve the things you are trying to do maybe.

Re: DAX Span of control

Anonymous — Thu, 03 Sep 2020 11:27:28 GMT

Greg_Deckler No, because that's what the database looks like 😞

Re: DAX Span of control

lbendlin — Thu, 03 Sep 2020 13:42:15 GMT

Do you want the solution in Power Query or in DAX? Why would you need a measure - I don't see how the result could be impacted by filters?

Re: DAX Span of control

Anonymous — Thu, 03 Sep 2020 13:44:29 GMT

lbendlin

DAX

I won't be using any filters I just need that raw table

Re: DAX Span of control

lbendlin — Thu, 03 Sep 2020 13:45:59 GMT

Let me rephrase. Do you need a measure or can it be a calculated column?

Re: DAX Span of control

Anonymous — Thu, 03 Sep 2020 14:46:59 GMT

lbendlin measure

Re: DAX Span of control

lbendlin — Thu, 03 Sep 2020 15:12:38 GMT

SOC := var h1 = SELECTEDVALUE(SoC[H1]) var h2 = SELECTEDVALUE(SoC[H2]) var h3 = SELECTEDVALUE(SoC[H3]) var h4 = SELECTEDVALUE(SoC[H4]) var h5 = SELECTEDVALUE(SoC[H5]) return switch(TRUE() ,h2="",CALCULATE(DISTINCTCOUNT(SoC[H2])-1,ALL(SoC),SoC[H1]=h1) ,h3="",CALCULATE(DISTINCTCOUNT(SoC[H3])-1,ALL(SoC),SoC[H2]=h2) ,h4="",CALCULATE(DISTINCTCOUNT(SoC[H4])-1,ALL(SoC),SoC[H3]=h3) ,h5="",CALCULATE(DISTINCTCOUNT(SoC[H5])-1,ALL(SoC),SoC[H4]=h4) ,0)

Note that your sample data is missing the "John -> Mark" row.

Re: DAX Span of control

Anonymous — Thu, 03 Sep 2020 16:24:14 GMT

lbendlin

SOC := 
var h1 = SELECTEDVALUE(SoC[H1])
var h2 = SELECTEDVALUE(SoC[H2])
var h3 = SELECTEDVALUE(SoC[H3])
var h4 = SELECTEDVALUE(SoC[H4])
var h5 = SELECTEDVALUE(SoC[H5])
return switch(TRUE()
,h2="",CALCULATE(DISTINCTCOUNT(SoC[H2])-1,ALL(SoC),SoC[H1]=h1)
,h3="",CALCULATE(DISTINCTCOUNT(SoC[H3])-1,ALL(SoC),SoC[H2]=h2)
,h4="",CALCULATE(DISTINCTCOUNT(SoC[H4])-1,ALL(SoC),SoC[H3]=h3)
,h5="",CALCULATE(DISTINCTCOUNT(SoC[H5])-1,ALL(SoC),SoC[H4]=h4)
,0)

That gave me -1 for all rows

Re: DAX Span of control

lbendlin — Thu, 03 Sep 2020 16:28:30 GMT

Is your main table's name "SoC" ? If not then you need to adjust the measure accordingly.

Re: DAX Span of control

Anonymous — Thu, 03 Sep 2020 16:30:23 GMT

lbendlin Yes I adjusted

Re: DAX Span of control

lbendlin — Fri, 04 Sep 2020 03:55:15 GMT

Here's an alternative version:

SOC := switch(TRUE() ,max(SoC[H5])>"",0 ,max(SoC[H4])>"",CALCULATE(DISTINCTCOUNT(SoC[H5]),REMOVEFILTERS(SoC[H5]))-1 ,max(SoC[H3])>"",CALCULATE(DISTINCTCOUNT(SoC[H4]),REMOVEFILTERS(SoC[H4]))-1 ,max(SoC[H2])>"",CALCULATE(DISTINCTCOUNT(SoC[H3]),REMOVEFILTERS(SoC[H3]))-1 ,CALCULATE(DISTINCTCOUNT(SoC[H2]),REMOVEFILTERS(SoC[H2]))-1 )

Re: DAX Span of control

Anonymous — Fri, 04 Sep 2020 13:09:47 GMT

lbendlin That still gives me -1 for all rows...

Re: DAX Span of control

lbendlin — Fri, 04 Sep 2020 13:44:30 GMT

That's most likely because you have a more involved data model. It works fine for a standalone table based on your sample data.