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 I

## All function with condition

Hello,

I have a date filter which I want to ignore on one column of the table, I cant use edit visualization for the whole table as other columns depends on date.

I want to show total sum of bottles a user owns which is irrespective of the date. But the problem is it interacts with date filter and only shows bottles which are bought by the user in selected month. So far I have tried using the following formula for making a calculated column, but it doesn't give desired result.

TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER(ALL(entries),entries[itemCode]="19LBottle"))

I have also used another method to solve it,l but still it doesnot provide me with correct results

Column1=CALCULATE(SUM(entries[itemQty]),entries[itemCode]="19LBottle"))

Column2=CALCULATE(SUM(entries[itemQty]),ALL(Column1)))

Any help would be appreciated.

1 ACCEPTED SOLUTION
Super User

Hi @mnarmeen,

As a good practice I ussually do a Calendar table to filter out my reports, however on your measure you are using all in the full table you need to only do the ALL for the date column you can try to make this change to your measure:

```TotalSale =
CALCULATE (
SUM ( entries[itemQty] ),
FILTER ( ALL ( entries[Date] ), entries[itemCode] = "19LBottle" )
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

9 REPLIES 9
Super User

Hi @mnarmeen,

You need to calculate this as a measure and not as a column, if you make your first formula in a measure and add it to your visuals should work, the ALL is a formula that is based on context so when you used it in a column the context is the row itself so no "filters" are applied but when you add that column to your reports the filter then kicks in and filter out the values that you also don't want.

Regards

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

And if I use this, it show me all the Qty without filtering it on the basis of customerid, which is present in the table

TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER(all(entries),entries[itemCode]="19LBottle"))

Super User

Is the date filter on the same table or on a Calendar table?

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Date filter is on the same table.

Super User

Hi @mnarmeen,

As a good practice I ussually do a Calendar table to filter out my reports, however on your measure you are using all in the full table you need to only do the ALL for the date column you can try to make this change to your measure:

```TotalSale =
CALCULATE (
SUM ( entries[itemQty] ),
FILTER ( ALL ( entries[Date] ), entries[itemCode] = "19LBottle" )
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Thankyou @MFelix,

However, I get the following error "A single value for column 'itemCode' in table 'entries' cannot be determined. This can happen when a measure formula refers to a column that contains many values"

Super User

Try this change to your code:

```TotalSale =
CALCULATE (
SUM ( entries[itemQty] ),
FILTER ( ALL ( entries[Date] ), MAX(entries[itemCode]) = "19LBottle" )
)```

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Helper I

Got it , Thanks alot !

Helper I

Thankyou @MFelix ,

Ive made this a measure in enteries table , but still it wont show me the values. It only shows me values when I choose ALL from my date slicer.

TotalSale=CALCULATE(SUM(entries[itemQty]),FILTER((entries),entries[itemCode]="19LBottle"))

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