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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AliceW
Impactful Individual
Impactful Individual

Total with 'Yes' in Column A while ignoring column B

This seems so simple, yet it's so frustrating. Any help would be welcome, please.

So, I have this table, 'Sales'. For simplicity' sake, it has three columns:

- Account

- Has Apples: Y/N

- Has Oranges: Y/N

One Account can have Apples, Orange, both, or none.

AccountHas ApplesHas Oranges
ANN
BYY
CYN
DNY
eNY

 

To calculate the number of Accounts with oranges, easy, I did get the right total of 3 using this formula:

= Calculate(DistinctCount[Account], Sales[Has Oranges] = "Y")

 

However, and this is where my problem lies, I want to also show this total of 3 when the 'Has Apples' filter is in the page set to 'Y'.

I tried REMOVEFILTERS(Has Apples). I just got a smaller number, 2, so the difference between one filter and the other. I tried ALL(Has Apples). Nothing.

 

If I put the top formula in a card and just disconnect it to the 'Has Apples' filter, I get 3. But I really need it in a table that is affected by it (the end result is a %)...

1 ACCEPTED SOLUTION

@AliceW So I mocked this up and it seems to work OK but I'm guessing there are additional things going on that may impact the solution. See attached PBIX below signature.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Syk
Super User
Super User

Did you try leaving your remove filters blank?

Accounts with oranges =
Calculate(DistinctCount(Sales[Account]), Sales[Has Oranges] = "Y",REMOVEFILTERS())
AliceW
Impactful Individual
Impactful Individual

It's crazy, I just get blanks. If I use ALL, it DOES work, but I have other columns in this table I need to use.

@AliceW What about substituting ALL with ALLSELECTED?


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@AliceW It is possible that you may run into an issue with Auto exist but try this:

Measure =
  COUNTROWS(
    DISTINCT(
      SELECTCOLUMNS(
        FILTER( ALL('Table'), [Has Oranges] = "Y" ),
        "__Account",
        [Account]
      )
    )
  )

@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
AliceW
Impactful Individual
Impactful Individual

Hi @Greg_Deckler, unfortunately, I get a pure Blank. Pulling my hair here...

@AliceW So I mocked this up and it seems to work OK but I'm guessing there are additional things going on that may impact the solution. See attached PBIX below signature.


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
AliceW
Impactful Individual
Impactful Individual

Thank you so much, Greg!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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