cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luketerry
Advocate II
Advocate II

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

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 III
Super User III

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




If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




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

AlB
Super User III
Super User III

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 

 

SU18_powerbi_badge

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 III
Super User III

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





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.