topic Re: Count values different than in DAX Commands and Tips

Count values different than

mic_rys — Wed, 31 Mar 2021 09:51:40 GMT

Hi, i need a daxhero!
I need to count Orders with a specific color and without it.

OrderID	Color	Product	Values
111	Blue	Prod1	1
111	Blue	Prod2	2
111	Red	Prod3	5
111	Green	Prod1	2
222	Red	Prod2	4
222	Green	Prod3	6
333	Red	Prod1	1
333	Red	Prod2	2
333	Blue	Prod3	5
333	Green	Prod1	2

For measure with color is easy:

BlueOrder = CALCULATE(DIstinctCount(OrderID), Color = "Blue")

Result is: 2 (ID 111 and 333)

But how to write measure for orders without Blue?

NoBlueOrder = ?

Result shoult be: 1 (ID 222)

i might add that it's live connection to SSAS tabular.

thank you in advance

Re: Count values different than

Jihwan_Kim — Wed, 31 Mar 2021 10:15:11 GMT

Hi, mic_rys

Please kindly check the below.

The sample pbix file's link is down below.

Without Blue =
VAR bluetable =
SUMMARIZE (
FILTER (
SUMMARIZE ( 'Table', 'Table'[OrderID], 'Table'[Color] ),
'Table'[Color] = "Blue"
),
'Table'[OrderID]
)
VAR alltable =
VALUES ( 'Table'[OrderID] )
RETURN
COUNTROWS ( EXCEPT ( alltable, bluetable ) )

https://www.dropbox.com/s/jw8dfslfo4zte4j/mic%20rys.pbix?dl=0

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

Re: Count values different than

mic_rys — Wed, 31 Mar 2021 12:01:24 GMT

thank you a lot, works perfectly!