All function with condition

[mnarmeen]

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.

[MFelix]

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

[mnarmeen]

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"))

[MFelix]

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

Regards,

MFelix

[mnarmeen]

Date filter is on the same table. 

[MFelix · Accepted solution]

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

[mnarmeen]

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"

[MFelix]

Try this change to your code:

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

Regards,

MFelix 

[mnarmeen]

Got it , Thanks alot !

[mnarmeen]

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"))