cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

if date1 = date2 then shows as blank else shows date2

Hi,

Need some help in bulding the logic below.

I have 2 tables related by ID. When showing on a table format, I want a new column showing if DATE1 = DATE 2, then shows as blank, else shows DATE 2.

 

This is what I have today:

 
IDDate 1 (Table 1)Date 2 (Table 2)
101/02/202004/02/2020
202/02/202002/02/2020
303/02/202003/02/2020
404/02/202007/02/2020

 

And this is what I need:

 

IDDate 1 New Column
101/02/202004/02/2020
202/02/2020 
303/02/2020 
404/02/202007/02/2020

 

Can somebody help me? Maybe a DAX formula with IF statemnt.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User VI
Super User VI

Re: if date1 = date2 then shows as blank else shows date2

If you have a table visual with ID and Date1 as columns, you could add a measure like this

 

Date2 Match = var date1value = selectedvalue(Table1[Date1])

var date2value = min(Table2[Date2]) //this assumes there is a single value in Table2 for this ID value

return if(datevalue1=datevalue2, blank(), datevalue2)

 

If there are multiple date values in Table2 for each ID, a different measure is needed and you'll need to describe the logic needed to get the desired Date2 value for comparison.

 

 





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

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Highlighted
Super User IX
Super User IX

Re: if date1 = date2 then shows as blank else shows date2

Should be:

 

New Column =
  VAR __Date1 = 'Table1'[Date 1]
  VAR __Date2 = MAXX(RELATEDTABLE('Table 2'),[Date 2])
  IF(
    __Date1 = __Date2,__Date2,BLANK()
  )
    

 

You might also have luck with:

New Column =
  VAR __Date1 = 'Table1'[Date 1]
  VAR __Date2 = MAXX(FILTER('Table 2','Table 2'[ID] = 'Table 1'[ID]),[Date 2])
  IF(
    __Date1 = __Date2,__Date2,BLANK()
  )

 

Really going to depend on the data in your tables and their relationship to one another but you have left out some of those details.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User VI
Super User VI

Re: if date1 = date2 then shows as blank else shows date2

If you have a table visual with ID and Date1 as columns, you could add a measure like this

 

Date2 Match = var date1value = selectedvalue(Table1[Date1])

var date2value = min(Table2[Date2]) //this assumes there is a single value in Table2 for this ID value

return if(datevalue1=datevalue2, blank(), datevalue2)

 

If there are multiple date values in Table2 for each ID, a different measure is needed and you'll need to describe the logic needed to get the desired Date2 value for comparison.

 

 





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

Proud to be a Super User!




View solution in original post

Highlighted
Anonymous
Not applicable

Re: if date1 = date2 then shows as blank else shows date2

If the two tables are related by ID and this is 1-to-1 relationship, then you should merge them into one table in Power Query. Then it'll be easy to do what you want. Also in Power Query.

Best
D
Highlighted
Frequent Visitor

Re: if date1 = date2 then shows as blank else shows date2

Hi @mahoneypat it worked like a charm :). Thank you a bunch.

 

In the same table I have also Amount 1 and Amount 2.

Is there any way I can create enrich these variables that if Date2value is blank then Amount2 is blank also?

 

I tried to use these variables you mentioned but couldn't do it.

Highlighted
Super User VI
Super User VI

Re: if date1 = date2 then shows as blank else shows date2

Yes.  Just reference the Date2 measure as follows:

 

Amount2 Measure = if(isblank([Date2Measure]), blank(), [Amount2 Measure])

 

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors