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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
khush19
Resolver I
Resolver I

Filter on datetime column sometime work ,sometime not

I am working on report ,where i need to take the amount based on latest datetime.

For this i created a measure

Total Calculated Amount = CALCULATE(sum(ParkingSessionSA[TotalAmount]),FILTER(ParkingSessionSA,ParkingSessionSA[CurrentTime]=MAX(ParkingSessionSA[CurrentTime])))
 
Somethimes measure show value ,sometimes not,not able to figure out why

 

Capture.PNG

9 REPLIES 9
Greg_Deckler
Super User
Super User

@khush19 Betting there is probably a reason. Unfortunately, not really enough information to go on. What exactly are you trying to accomplish with that measure because it seems like it could just be:

sum(ParkingSessionSA[TotalAmount])

 

Please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

HI Greg,

 

I have data like :

Capture1.PNG

now i need to get Amount based on max (currenttime).

For this i created a measure and this measure is used in many measure to calculate difference ,% change.

and the worst part is it,this measure works sometimes and sometimes not.

and it work in SIT,UAT and fail only in PROD 😞

@khush19 First, not typing that data into an Enter Data query to mock this up, please post as text.

 

Second, what do you mean by the MAX of currenttime? If you have a measure and you put it in a visual along with currenttime column then if you get the MAX of the currenttime, you are getting that row's currenttime, which is already in context so you don't need to filter on it because that row in the table visualization and already filtered to that currenttime which is why your formula seems like it is equivalent to SUM([totalamount])

 

In other words, if I had data like:

 

Color

Red

Orange

Blue

 

And I create a measure like:

Measure = MAX([Color])

 

This would return the exact same thing as:

Measure = CALCULATE(MAX([Color]),FILTER('Table',[Color]=MAX([Color])))

 

So, should your measure work? Yes, but it is unnecessarily complicated.

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

 

I am using Card,to investigate the issue i created table.

and seems like for some rows even i compare int column in max ,then also it doesnt return desired result

Hi @khush19 

 

You might consider providing your dummy pbix that would be helpful for us to investigate it further. 

You can upload it to the onedrive for business and share the link here. please don't forget to disclose the expected results and remove the confidential info.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

@khush19 , You expected output is not clear. Max on the row context as good as row values unless there is some logic added

Try

Total Calculated Amount =
var _max =MAXX(ParkingSessionSA,ParkingSessionSA[CurrentTime])
return
CALCULATE(sum(ParkingSessionSA[TotalAmount]),FILTER(ParkingSessionSA,ParkingSessionSA[CurrentTime]=_max))

 

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

HI Amit,

 

I tried the measure you suggested,even that is also not working.

@khush19 OK, has to be an issue with your data then. Is this import mode or Direct Query mode or?



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

This is PUSH dataset ,where data is pushed to power BI every 5 mins from Azure data factory

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.