Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
esbertl
New Member

Wrong calculation using date and text filter

I have a small sample project using this csv data ("testvalues.csv")

DateChanged,Value,Project
2024-01-01 00:00:00,100,Project A
2024-01-01 00:00:00,20,Project B
2024-01-10 00:00:00,-10,Project A
2024-01-11 00:00:00,10,Project A
2024-01-15 00:00:00,10,Project A
2024-01-15 00:00:00,5,Project B
2024-01-20 00:00:00,5,Project B
2024-01-25 00:00:00,10,Project A
2024-01-25 00:00:00,10,Project B

And I have added a mesure calulating the sums for every day:

ValueOnDate = CALCULATE(SUM(testvalues[Value]), FILTER ( ALL( testvalues[DateChanged] ), testvalues[DateChanged] <= MAX ( testvalues[DateChanged] )))
I can now visualize this data and even filter it by Project and it works just fine.
If I just filter "Project A" it looks like this:
esbertl_0-1712307427732.png

If I filter "Project B" all correct values are calculated correctly as well:

esbertl_1-1712307502596.png

And if I don't filter for projects or set both projects or if i choose "select all" everything works fine:

esbertl_2-1712307598345.pngesbertl_3-1712307611662.pngesbertl_4-1712307624428.png
The calculated values are:
esbertl_6-1712307915899.png

 



But if I add an additional filter on the date (even without filtering data) things are getting confusing:

esbertl_5-1712307717208.png

 

If I now filter for both projects (without selecting "Select all") all rows are wrong having just a value for one project on a day:

esbertl_7-1712307947651.png

 

esbertl_8-1712308195372.png

 

Do you have a solution for me?
 
1 ACCEPTED SOLUTION

Hi esbertl,

 

The references to a date column in your measure should now reference the new date dimension table. 🙂

 

ValueOnDate = 
CALCULATE (
    SUM ( testvalues[Value] ), 
    FILTER ( 
        ALL ( 'Calendar'[Date] ), 
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

View solution in original post

5 REPLIES 5
esbertl
New Member

Hi Wilson!

Works great! Thanks a lot!

Awesome, thanks for the update esbertl. Happy I could help! 😄

esbertl
New Member

Thank you Wilson for the quick answer!

In fact, with the help of the table, the strange calculation behavior is gone, unless I use the date table in the filter.


The basic idea of the filter on the date is to display only a certain section of the data. However, if I now select 02.01. as the start date, the values before 01.01. are missing from the total. I assume that I have to adjust my measure. Do you have any hints as to how the measure should look?

esbertl_1-1712557519943.png

esbertl_2-1712557543853.png

 

 

 

 

Hi esbertl,

 

The references to a date column in your measure should now reference the new date dimension table. 🙂

 

ValueOnDate = 
CALCULATE (
    SUM ( testvalues[Value] ), 
    FILTER ( 
        ALL ( 'Calendar'[Date] ), 
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
    )
)


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Wilson_
Memorable Member
Memorable Member

Hi esbertl,

 

I strongly encourage you to build out a separate date table instead of having just one data table. Your problem goes away if you do.

 

Wilson__0-1712326166289.png

 

Doing so is a best practice and you're seeing one of the reasons why. You can sometimes have unintended filtering side effects otherwise.


----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

 

P.S. Need a more in-depth consultation for your Power BI issue? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.