How to count consecutive columns to count the values with non blank cells

[MayankMit1625]

Here Plant1, Plant 2 etc tells how many operation does Company has under that Plant.

I want to calculate the "Net" value has the COUNT of cells that are filled from Plant1 to Plant 5 and then calculate the total number of rows under "Net" which has all 5 plants 

Final result is "Total together" i.e. Organization that has operation in all the 5 plants.

Total Together counts the number of "5" in Net column

I used measures to calculate the values but wasn't able to get to the solution.
Please help!

[V-lianl-msft · Accepted solution]

Hi @MayankMit1625 ,

In the query editor, select the org column then unpivot other columns:

Then create 2 measures like below:

Net = 
 CALCULATE(DISTINCTCOUNT('Table'[Attribute]),ALLEXCEPT('Table','Table'[Org Name]))


Total Together = CALCULATE(DISTINCTCOUNT('Table'[Org Name]),FILTER('Table',[Net]=5))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

You really should consider unpivoting your data to simplify your analysis and visualization. The unpivot action will remove any combinations with null, and then you can do a simple DISTINCTCOUNT of the Attribute (Plant) column, or FILTER for organizations where the DISTINCTCOUNT of Plant = 5.

Pat