cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wiepsie
New Member

Running total not working with extra filter

Hi everyone,

 

I've created a running total which is working fine. However, if I use that measure and add another filter in another measure, the running total is not working.

 

The running total formula is:

Running total = CALCULATE(SUM(FactActuals[Waarde/CO-valuta]);filter(ALLSELECTED(DimCalendar);DimCalendar[Date]<=MAX(DimCalendar[Date])))

The formule with the added filter is:

Running total with extra filter = CALCULATE([Running total];filter(FactActuals;FactActuals[Name]="Mr.X"))

So the 2nd formula is the same as the first one, expect for the added filter.

However the 2nd formule doenst have the running total, but has single values for each year (see screenshot below).

I don't understand the logic.

screenshot.jpg

The following formula is working, but is not the formula that I want to use.  I would like to use the filter option.

Running total with extra filter = CALCULATE([Running total];FactActuals[Name]="Mr.X")

Could someone help me out with explaining why the 2nd formula isnt working as expected?

Thanks!

 

1 ACCEPTED SOLUTION
kentyler
Solution Sage
Solution Sage

When you write a table expression into a CALCULATE it is always transformed into a FILTER here is a quote from a good post on this

 

When you write a CALCULATE statement, all the filter arguments are table expressions, such as a list of values for one or more columns, or for an entire table. For example, when you write:

1
2
3
4
CALCULATE (
    <expression>,
    table[column] = <value>
)
COPYDAX CONVENTIONSCODE #1 FORMAT CODE WITH 

In reality the filter expression you wrote is transformed in:

1
2
3
4
5
6
7
CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value>
    )
)

 

so when you write this it is then transformed again

filter(FactActuals;FactActuals[Name]="Mr.X"


So the formula you "want to use" is the one that DAX transforms your calculate statement into automatically... you don't have to explicitly write "Filter"





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

1 REPLY 1
kentyler
Solution Sage
Solution Sage

When you write a table expression into a CALCULATE it is always transformed into a FILTER here is a quote from a good post on this

 

When you write a CALCULATE statement, all the filter arguments are table expressions, such as a list of values for one or more columns, or for an entire table. For example, when you write:

1
2
3
4
CALCULATE (
    <expression>,
    table[column] = <value>
)
COPYDAX CONVENTIONSCODE #1 FORMAT CODE WITH 

In reality the filter expression you wrote is transformed in:

1
2
3
4
5
6
7
CALCULATE (
    <expression>,
    FILTER (
        ALL ( table[column] ),
        table[column] = <value>
    )
)

 

so when you write this it is then transformed again

filter(FactActuals;FactActuals[Name]="Mr.X"


So the formula you "want to use" is the one that DAX transforms your calculate statement into automatically... you don't have to explicitly write "Filter"





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.