Forum Discussion

jwin2424's avatar
jwin2424
Resolver I
4 years ago
Solved

Distinct Count if ALL rows have the exact same value

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 Co...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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 🙂

     

TheoC's avatar
TheoC
Community Champion
4 years ago

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's avatar
jwin2424
Resolver I
4 years ago

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

  • TheoC's avatar
    TheoC
    Community Champion
    4 years ago

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