Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Running total not working with extra filter

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Running total not working with extra filter

09-28-2019
07:32 AM

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.

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2019
09:12 AM

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

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"

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

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

1 REPLY 1

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2019
09:12 AM

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

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"

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

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

Announcements

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

Mark your calendars and join us for our next Power BI Dev Camp!

Featured Topics

Top Solution Authors

User | Count |
---|---|

238 | |

131 | |

82 | |

60 | |

54 |

Top Kudoed Authors

User | Count |
---|---|

275 | |

174 | |

90 | |

88 | |

83 |