cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbipro007
Helper I
Helper I

Comparing dates in the maximum Selected date with the column of the table via If and Else statement

I am trying to compare the dates in the maximum Selected date with the column of the table via If and Else statement and it is not working as expected. It seems like somehow it is not recognizing the measure as dates. 

 

My Measure to have the maximum date selected: 

MaxPostDateSelected = CALCULATE(MAX((PostDate[Date])),ALLSELECTED(PostDate[Date]))
 
If and else statement for the table Maint with the Post_date as the column:
 
PostDateCheck = if(Maint[Post_Date]>=[MaxPostDateSelected],"YES","NO")
 
The formula runs but it is not working as expected. 

 

11 REPLIES 11
Anonymous
Not applicable

Of course it's not working. Columns in data model tables - because I gather you're talking about such a table - are static. They don't react to selections and measures after the columns have been calculated.

Thank you for your reply. Then what you suggest?

Anonymous
Not applicable

I can't suggest anything because I don't know what you want to achieve.

For your information, I want to filter the table based on the minimum and maximum selected date value from the user and create the graphs etc based on that. 

 

I am trying for Calculate table as well but somehow that is not working as well. 

 

I have a SSRS report where user selects a date and then based on that same date two different tables gets filtered and the report gets shown. I am trying to create that same report in Power BI. 

 

Anonymous
Not applicable

Power BI does not work the way you think it does. Power BI is all about measures and relationships. If you have a slicer whose values are taken from a column in a table, this column gets filtered automatically by the values of the slicer. You don't have to do anything else. For SSRS reports you have to use DAX to build a query to retrieve data (use SUMMARIZECOLUMNS). It's just like with SQL but DAX is more concise.
Anonymous
Not applicable

If you're totally now to Power BI, I'd strongly suggest you first try to learn the basics. You can start with YT vids. There are myriads of them.

Thank you I know that! I may not be that new like you are thinking I am. I have tried to search for answers and could not find the one I am looking for that is why I posted this and I think is the genuine post which lots of people might be searching answers for. I know what slicers can do. 

If you do not know the exact answer then it would be better not to reply.

Thanks!

mahoneypat
Microsoft
Microsoft

You need to aggregate that column somehow in your measure.  For example,

 

PostDateCheck = if(MAX(Maint[Post_Date])>=[MaxPostDateSelected],"YES","NO")

 

Even though there is a single value on that row, you still need an aggregation.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you for your quick reply. It is somehow still now working. 

Please see in the pictures. The first one is giving the selected date for the measure MaxPostDateSelected and the other one is the table that I am trying to use the formula to. 

MaxPostDate.JPG

 

PostDateCheck_NotWorking.JPG

HI @powerbipro007 ,

 

 

Columns in Tables are static and they do not respond to slicers which generate value of the selected date dynamically.

 

A measure will helps you  in such cases.

 

Try to create a measure and this will work.

 

PostDateCheck = if(MAX(Maint[Post_Date])>=[MaxPostDateSelected],"YES","NO")

 

Hope this helps you.

 

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

 

 

 

Thanks!

But, I will not able to filter the page with the measure though. 

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!