cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

dax NOT in

Helllo,

rather than writing out all this in example 1 (which gives correct result), how do I specify the NOT in (i want to count everything in table[sport] column except "09" and "99"

1. this gives right result

``````my measure =
CALCULATE(count(table[sport]),
table[sport] in {"01","02","03","04","05","06","07","08","10","11","12"},
table[year] = "2017/18")``````

do you know why these three examples below don't work?

2.

``````test =
CALCULATE(count(table[sport]),
(table[sport]<> "09" &&
table[sport]<> "99"),
table[year] = "2017/18")``````

3.

``````test2 =
CALCULATE(count(table[sport]),
not(table[sport] in {"09","99"}),
table[year] = "2017/18")``````

4.

``````test3 =
CALCULATE(count(table[sport]),
table[sport] <> "09",
table[sport] <> "99",
table[year] = "2017/18")``````

Thanks

7 REPLIES 7
Community Champion

Can you try this:-

``````test3 =
CALCULATE (
COUNT ( table[sport] ),
FILTER (
table,
( table[sport] <> "09"
|| table[sport] <> "99" )
&& table[year] = "2017/18"
)
)``````

Thanks,

Samarth

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Helper V

@Samarth_18 I tried but this didn't give me the result I was expecting

Community Champion

@adavid999 , Can you please share your PBIX after removing sensitive data and also the expected output?

Best Regards,
Samarth

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Super User

@adavid999 You mean like NOT("something" IN 'Table'[Column])

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Helper V

@Greg_Deckler hi there, yes, in this case i want to count everything that is not "09" and not "99" in table[sport] where availabe values are:

``"01","02","03","04","05","06","07","08","09","10","11","12","99"``

Just lookinf for a better way of writing it as my example 1 above, does the job. Also, I'm not 100% clear on why my other examples don't work.

Super User

maybe you can try this

``````test3 =
CALCULATE(count(table[sport]),
(table[sport] <> "09"||
table[sport] <> "99" &&
table[year] = "2017/18")``````

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Helper V

hi @ryan_mayu no this did not work. Your suggestion...

```test3 =
CALCULATE(count(table[sport]),
(table[sport] <> "09"||
table[sport] <> "99" &&
table[year] = "2017/18")```

gives a different result to this (which gives the result I expect)

```my measure =
CALCULATE(count(table[sport]),
table[sport] in {"01","02","03","04","05","06","07","08","10","11","12"},
table[year] = "2017/18")```

I want to count values that are not "09" or "99" from available values that are:

`"01","02","03","04","05","06","07","08","09","10","11","12","99"`

grateful for any help with this.

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors