Need to find matching names in two tables and date in one column greater than the other

[v-gizhi-msft · Accepted solution]

Hi,

Please try to create this calculated column in 'Empolyee' table first:

Column = DATEDIFF(RELATED(Contractor[Hire Date]),Employee[Hire Date],DAY)

Then try this color measure:

Color = IF(MAX(Employee[Column])=MINX(FILTER(ALL(Employee),Employee[Column]>=0),Employee[Column]),"#FF0000")

Apply this measure to the 'Employee'[Name] field, the result shows:

 

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

Create a common employee name dimension. Now take date diff from both tables and use that to do conditional formatting

refer how to take date diff

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

For highlight : https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[nandukrishnavs]

@Anonymous 

Try the below-calculated column 

StatusCheck = 
var _contractHireDate=LOOKUPVALUE(ContractorTable[Hire Date],ContractorTable[Name],EmployeeTable[Name])
var _hireDate= EmployeeTable[Hire Date]
var _result= IF(ISBLANK(_contractHireDate),"Not Present in Contractor Table",IF(_hireDate>_contractHireDate,"Employee hire date is greater than contractor hire date","Employee hire date is less than contractor hire date"))
return _result

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

@nandukrishnavs  hi im getting a error that "A table of multiple values was supplied where a single value was expected." for this dax measure

[nandukrishnavs]

@Anonymous  Did you create measure or calculated column?

[camargos88]

Hi @Anonymous ,

Try this one:

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