Distinct Count if ALL rows have the exact same value

[jwin2424]

Hello. 

I am trying to determine how many distict orders have ONLY the data type "potential."

Here is the output count I am needing:

Distinct Count with at least one actual	Discint Count with no actuals
3	3

Here is the data

Order ID	Data Type
101	potential
101	potential
101	potential
101	potential
101	potential
101	potential
101	potential
543	potential
543	potential
543	potential
543	actual
543	actual
543	potential
543	potential
543	potential
543	potential
754	potential
754	potential
754	potential
246	actual
246	actual
246	actual
246	potential
246	actual
269	actual
269	actual
269	actual
269	actual
269	actual
231	potential
231	potential
231	potential
231	potential

I am able to get a distinct count of orders with the word "actual" in it, but if I filter by "potential", you will get orders that have both "potential" and "actual:". Thought that is fine for my model with the "actual", I need to know how many orders do not contain the word "actual" on any of the lines.

Thanks!

[jwin2424]

Thank you for the quick reply! That did get me halfway there. However, when I add the data type into the rows so I see which orders only have potential, it is still calculating rows that have actual. The TOTALS work, but not at the row level

[TheoC]

Hi @jwin2424 

In your post, you asked for the distinct counts based on requirements and provided theoutputs you require.  If there are other requirements you need, please add these to the original post to ensure that the solutions are provided in the most resourceful way possible.

In terms of what you are after, Matrix visuals do not work as you have presented.  Your matrix visual has underlying rows (hence the +) and Power BI is calculating those records too.  Basically, Power BI is doing what you are asking it to do in this instance.

If you remove the underlying records, and bring the measures into your matrix at the Row Level, turn on the Switch Values to Rows option, you will get 3 and 3.

[jwin2424]

I see. I removed the actual/potential rows and being able to switch to rows not columns helped. Thank you. This solution worked

[TheoC]

@jwin2424 no worries mate.  Just a heads up, and as silly as this sounds, Matrix and Table visuals work very differently in Power BI.  It is one of the more complex areas in Power BI because what would normally be considered "logical" in one, is not necessarily the case for the other.  Nonetheless, they each serve a solid purpose but just be conscious of the nuances in each (especially with Matrix visual and Subtotals / Totals).

[TheoC · Accepted solution]

Hi @jwin2424 

You can use the following two measures:

DCount Actual = 

VAR _1 = COUNTROWS ( FILTER ( DISTINCT ( Table[Order ID] ) , [Actual] > 0 ) )

RETURN

_1

DCount No Actual = 

VAR _1 = COUNTROWS ( FILTER ( DISTINCT ( Table[Order ID] ) , [Actual] = 0 ) )

RETURN

_1

Output will be as per below:

Hope this helps!

Theo 🙂