cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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.


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

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

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!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

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.