How to match two unrelated dates tables using DAX ?

[Abhanshu97]

We have two tables - Table A and Table B

Table A contains the column of Employee ID(EID) and Date of Joining(DOJ).

Table B contains the column of Dates(Date) only.

There is no relationship between two tables.

Please suggest me how do I match the two tables such that if we select date slicer from Table B then it would change the data of Row card containing count of EID ?

I have tried using Lookupvalue but it didn't work.

Any help would be appreciated!!

[v-rzhou-msft · Accepted solution]

Hi @Abhanshu97 

You can try to build a measure to calculate the count of Employee ID in Table A based on the filter of unrelated Date table.

Table A:

Date Table:

Date = CALENDARAUTO()

They are unrelated.

If you want to select dates directly in date slicer, try this measure.

Measure = 
VAR _SelectValue = VALUES('Date'[Date])
VAR _Count = CALCULATE(COUNT('Table A'[Employee ID]),FILTER(ALL('Table A'),'Table A'[Date] in _SelectValue))
RETURN
_Count

By default, no selection in date slicer, result should return to 8.

In silcer I select 2021/05/10 and 2021/07/05, result should return to 3.

If you want to filter Table A by between function in unrelated date slicer, try this measure.

Measure 2 = 
VAR _RangeStart = MIN('Date'[Date])
VAR _RangeEnd = MAX('Date'[Date])
VAR _Count = CALCULATE(COUNT('Table A'[Employee ID]),FILTER(ALL('Table A'),AND('Table A'[Date]>=_RangeStart,'Table A'[Date]<=_RangeEnd)))
RETURN
_Count

By default:

Range between 2021/05/10 and 2021/07/05:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[v-rzhou-msft · Accepted solution]

Hi @Abhanshu97 

You can try to build a measure to calculate the count of Employee ID in Table A based on the filter of unrelated Date table.

Table A:

Date Table:

Date = CALENDARAUTO()

They are unrelated.

If you want to select dates directly in date slicer, try this measure.

Measure = 
VAR _SelectValue = VALUES('Date'[Date])
VAR _Count = CALCULATE(COUNT('Table A'[Employee ID]),FILTER(ALL('Table A'),'Table A'[Date] in _SelectValue))
RETURN
_Count

By default, no selection in date slicer, result should return to 8.

In silcer I select 2021/05/10 and 2021/07/05, result should return to 3.

If you want to filter Table A by between function in unrelated date slicer, try this measure.

Measure 2 = 
VAR _RangeStart = MIN('Date'[Date])
VAR _RangeEnd = MAX('Date'[Date])
VAR _Count = CALCULATE(COUNT('Table A'[Employee ID]),FILTER(ALL('Table A'),AND('Table A'[Date]>=_RangeStart,'Table A'[Date]<=_RangeEnd)))
RETURN
_Count

By default:

Range between 2021/05/10 and 2021/07/05:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Abhanshu97 · Accepted solution]

Hello @v-rzhou-msft,

Thanks a ton, for your help. I really appreciate it.

[Portrek]

Hi Abhanshu97

Make a relationship between the tables, join the Date[date] and Date of joininid columns. By doing this the slicer will change your row card

Best regards

[Abhanshu97]

Thanks Portrek!

But here the problem is that, we can't create the relationship between tables. I mean we only have to use DAX to match the tables.

Is that be possible to do so with DAX?

[Portrek]

Without relationships I find it very difficult to get. Good luck !