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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NiugeS
Helper V
Helper V

If Filtered Date is Smaller then blank

Hi all,

 

Apologies if this is simple but i'm still learning.  

 

I have a table that contains issues (support tickets).  Sometimes these are resolved after the report period and I am trying to find a way that if the date is bigger then the Created Before slicer, it returns a blank value.  There's a measure included to keep blank resolved dates and I assume that measure could be modified.

 

Measure 2 is currently = If(MIN('Issues'[Created Date])>min('Created Date'[Date]),1,0) as I test but if the value is true I want blank returned and if false the 'Issues'[Resolved Date] returned.  I've attached the pbix for reference Pbix File 

Any help or guidance appreciated.

 

Screenshot_1.jpg

 

16 REPLIES 16
AllisonKennedy
Super User
Super User

Measure 2 = If(MIN('Issues'[Created])<min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created]) )
 
That will give you equal or before selected date in Created Before slicer.
 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

az38
Community Champion
Community Champion

Hi @NiugeS 

= If(MIN('Issues'[Created Date])>min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created Date]) )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thank you for the help.  I tried to use the suggested measure but the table comes back blank.  Am I doing somethign wrong?

 

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), BLANK(), SELECTEDVALUE('Issues'[Created]) )

 

Screenshot_3.jpg

 

@NiugeS  Your sample pbix file doesn't match your description? 

 

Try replacing BLANK() in the formula with a value like "A" and try putting the second argument in Selectedvalue, like: 

 

SELECTEDVALUE('Issues'[Created], "B")

So final result will be; 

 

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), "A", SELECTEDVALUE('Issues'[Created], "B") )
 

What does the formula return now? 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@AllisonKennedy  Apologies .. i've udpated the sample pbix file.

 

I tried the formula provided but get all A in the table despite changing the dates in the slicer:

 

Screenshot_4.jpg

As you have alread mentioned, you need to use the right dates. Change the dates in the Created Before slicer and you will see the A change. If you're seeing 'A" that means that the condition in your IF statement is TRUE, if you see 'B' that means the condition in your IF statement is false and there is more than one value selected for that date.

 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 

 

If you found this post helpful, please give Kudos.

I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 

https://sites.google.com/site/allisonkennedycv


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

az38
Community Champion
Community Champion

@NiugeS 

and are you sure you attached the correct pbix-file?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn
az38
Community Champion
Community Champion

@NiugeS 

try also

Measure 2 =
If(MIN('Issues'[Created])>min('Created Date'[Date]), BLANK(), MIN('Issues'[Created]) )

it depends what exactly  'Issues'[Created Date]  you need. Measure shoud has aggregated column 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Thanks for coming back to me.  I'm not sure what i'm doing wrong but neither measure provide any results.

az38
Community Champion
Community Champion

@NiugeS 

please, attach correct file


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 Apologies if I copied the wrong file link.  I've updated the original message.

az38
Community Champion
Community Champion

@NiugeS 

much better

1 set Active "Show tems with no data" for [Key] field in Visualizations pane

2 re-write your measure

Measure 2 = 
var _Created = MIN('Issues'[Created])

RETURN
If(_Created > CALCULATE(MIN('Created Date'[Date])), BLANK(), SELECTEDVALUE('Issues'[Created]) )

 

but Im not sure I understand your condition logic correct

 


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 I think I should shut down the laptop for the night!  I didn't explain my logic correctly ... i wanted resolved date as you probably guessed!  


I will work on the suggestion you provided and see if I can make sense of it.  Thank you.

az38
Community Champion
Community Champion

@NiugeS 

Measure 2 = 
var _Created = MIN('Issues'[Created])
var _Resolved = MIN('Issues'[Resolved Date])

RETURN
If(_Created > _Resolved , BLANK(), _Created )

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@az38 

Thank you for your help.  I modified your previous suggestion before seeing the new one.  The below seems to provide the desired results.  Thank you for all your help.  I'll try the other measure as well - thanks.
Screenshot_5.jpg

 

 

az38
Community Champion
Community Champion

@NiugeS 

good luck! have a good night 🙂


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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