cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sdg8481 Frequent Visitor
Frequent Visitor

FILTER(ALL on multiple

Hi,

 

I have a simple table with Date, Product Name and Value. I need a measure that will provide a Total 'value' for the dates <=  a date chosen by the user, of which the following works fine:

 

Measure = CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALL('0 - Measureables'),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))

 

HOWEVER, this completely ignores the 'Product Name' slicer (i assume because its referencng ALL. How can i adapt this so that the product name slicer is still filtered. Therefore giving a total for All previous dates for the chosen product name too.

 

Many Thanks

Stu

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: FILTER(ALL on multiple

@Sdg8481,

Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.

So we can create a measure using the formula as below:

ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))

After that we can get the result that we need.

 1.PNG

For more information, please refer to the pbix as attached.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
tex628 New Contributor
New Contributor

Re: FILTER(ALL on multiple

If you simply want to display the total value for a specific set of dates, cant you use a date slicer together with a "measure = sum(measureables[value])?

Sdg8481 Frequent Visitor
Frequent Visitor

Re: FILTER(ALL on multiple

Hi, thank you for the response. Unfortunately though i need to display sum of the values (total) for each measure but only until the date that the user selected, not all the dates in the table as some may be after this point. In the seperate column i have a measure showing exactly what you have suggested, but that only gives the total for the chosen day.

 

Hope this makes sense,

 

Thanks

 

Moderator v-yuezhe-msft
Moderator

Re: FILTER(ALL on multiple

@Sdg8481,

Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.

So we can create a measure using the formula as below:

ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))

After that we can get the result that we need.

 1.PNG

For more information, please refer to the pbix as attached.



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Sdg8481 Frequent Visitor
Frequent Visitor

Re: FILTER(ALL on multiple

Brilliant Thank you.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 76 members 1,160 guests
Please welcome our newest community members: