Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
adavid999
Helper V
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
Samarth_18
Community Champion
Community Champion

Hi @adavid999 ,

 

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.
Appreciate your Kudos!!
Connect on Linkedin

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

@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.
Appreciate your Kudos!!
Connect on Linkedin

Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

@adavid999 

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!




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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.