Date filter All() Error: "A table of multiple values was supplied where a single value was expected"

[luketerry]

Hi,

I have a very simple measure that I've been stuck on.

I want to sum the $ amount of a table based on 3 conditions + remove the date slicer on the report page.

Relationship is the Date Table is linked to a PnL Data table by date.  The Date table has a slicer.

Here's my measure:

CALCULATE(SUM(PnL[$]),

FILTER(PnL,PnL[Type]="Asset" && PnL[Class] <> "Overhead" && PnL[Sold?] = "False" -- These are the 3 conditions I'm filtering for

&& ALL(PnL[Date]))) -- I want to remove the filter from the Date Table slicer

I've tried every combination and placement I can think of to utilize the ALL(), REMOVEFILTERS(), and FILTER(ALL()) functions but the only result I get is either that it doesn't remove the date filter or I get an "A table of multiple values was supplied where a single value was expected." (which is what occurs in the above example)

I think it's something obvious I'm missing but I can't tell what.

Thanks for your help

[Pragati11]

Hi @luketerry ,

I will suggest writing your dax as follows:

CALCULATE(SUM(PnL[$]),

FILTER(ALL(PnL[Date]),PnL[Type]="Asset" && PnL[Class] <> "Overhead" && PnL[Sold?] = "False"))

[luketerry · Accepted solution]

Thanks Pragati11.

I did try a modification of this function with mixed results. When using Filter(ALL(Date), then the only field that can be filtered is date.

Here's what I did, however some of my detail views didn't submit up everything properly at the detail level.

CALCULATE (SUM (PnL [$]),

FILTER (ALL (PnL [Date]),1=1), PnL [Type] = "Asset" && PnL [Class] <> "Overhead" && PnL [Sold?] = "False")

[AlB]

Hi @luketerry 

Try

CALCULATE (
    SUM ( PnL[$] ),
    FILTER (
        PnL,
        PnL[Type] = "Asset" && PnL[Class] <> "Overhead" && PnL[Sold?] = "False"
    ),
    ALL ( PnL[Date] )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[luketerry]

Thanks A|B,

Unfortunately this doesn't work either.  I even simplified the structure by removing the 'date table' and switched the date slicer from the 'date table' to the date field in the PnL data.

This is the result I get when I have ALL(PnL[Date])) - it still filters to only be 12/1/2020 data

[Pragati11]

Hi @luketerry ,

Can you kindly share the exact syntax of your measure please ; in order to understand the exact error.?

Currently I can't understand it completly as you have split it into two.

Also, try sharing some sample data.

Thanks,

Pragati