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
Anonymous
Not applicable

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

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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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")
What I ended up doing is in m query created a copy of the data set and grouped the data and removed the dates from that grouped table. This eliminated the problem but it seems like I should have been able to have solved this within DAX.
Thanks for your help

View solution in original post

5 REPLIES 5
Pragati11
Super User
Super User

Hi @Anonymous ,

 

I will suggest writing your dax as follows:

CALCULATE(SUM(PnL[$]),

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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")
What I ended up doing is in m query created a copy of the data set and grouped the data and removed the dates from that grouped table. This eliminated the problem but it seems like I should have been able to have solved this within DAX.
Thanks for your help
AlB
Super User
Super User

Hi @Anonymous 

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 

 

SU18_powerbi_badge

Anonymous
Not applicable

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

luketerry_0-1609777328959.png

 

Pragati11
Super User
Super User

Hi @Anonymous ,

 

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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.