DAX Span of control

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[lbendlin]

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?

[Anonymous]

@lbendlin 

DAX

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

[lbendlin]

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

[Anonymous]

@lbendlin measure

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

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

[Anonymous]

@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

[lbendlin]

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

[Anonymous]

@lbendlin Yes I adjusted

[lbendlin]

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
)

[Anonymous]

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

[lbendlin]

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