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.

 

 

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.


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

Putting square pegs in round holes since 1972.

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.

 

 

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.