topic Re: Create a measure that counts values where a condition complies between columns of 2 tables. in DAX Commands and Tips

Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sat, 09 Nov 2019 19:31:03 GMT

Hi all.

I have two tables A and B joined by an ID.

On each of the tables, there is a date column.

I'm trying to create a measure that counts the number of rows in which the date of table A is between the date in table B and the date in table B +1 day.

Table A

ID DateA

1 2017-03-02

2 2018-06-03

3 2019-05-13

4 2017-08-23

Table B

ID DateB

1 2017-03-01

2 2018-06-03

3 2014-05-28

4 2015-08-23

In this case, Id 1 and 2 have dates that comply with the condition, so the expected value of the measure should be 2.

Thanks in advance

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Nathaniel_C — Sun, 10 Nov 2019 03:34:04 GMT

Hi Anonymous ,
I believe it is 1 and 4 IDs that work. The largest value in table B is 2018 6 3, so ID 2 3 in table A are over the limit for the upper value. The first measure works on a table, but to show it on a card it took a calculated column and the second measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

A measure:

Between Dates = Var _curDate = MAX(TableA[Date]) var _calcMinDate = Calculate(MAX(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]<_curDate) var _calcMaxDate = Calculate(Min(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]>_curDate) var _calc = CALCULATE([Countrows TA],FILTER(TableA,_curDate>_calcMinDate && _curDate<_calcMaxDate)) return _calc

A Calculated Column:

Between = Var _curDate = (TableA[Date]) var _calcMinDate = Calculate(MAX(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]<_curDate) var _calcMaxDate = Calculate(Min(TableB[Date]),ALLEXCEPT(TableB,TableB[Date]),TableB[Date]>_curDate) var _calc = CALCULATE(MIN(TableA[ID]),FILTER(TableA,_curDate>_calcMinDate && _curDate<_calcMaxDate)) return _calc

A measure to display the Calculated Column on a card.

Sum of rows = CALCULATE(COUNTROWS(TableA),TableA[Between]<>BLANK())

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sun, 10 Nov 2019 09:57:57 GMT

Hi Nathaniel,

Thanks for the answer, probably I didn't explain myself properly.

You took the max and min date values of table B and counted all the values from table A in which the date is between those values.

What I want is to apply the condition on every row given that both tables are joined in the model by the ID.

i.e

Table A Table B

DateA ID DateB

2017-03-02 1 2017-03-01 is DateA between DateB and DateB+1day? YES

2018-06-03 2 2018-06-03 is DateA between DateB and DateB+1day? YES

2019-05-13 3 2014-05-28 is DateA between DateB and DateB+1day? NO

2017-08-23 4 2015-08-23 is DateA between DateB and DateB+1day? NO

So the measure should return a Value=2, that belongs to Ids 1 and 2.

I hope this way is better explained.

Thanks for helping anyway.

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sun, 10 Nov 2019 15:19:08 GMT

Hi @Nathaniel,

Thanks for the answer, probably I didn't explain my self properly.

You picked the min and max values on TableB and use them to filter all values on tableA.

What I want to do is apply a filter in row 1 of TableA depending of the value on TableB using the ID that is the field used to join both tables in the model.

Table A TableB

DateA ID DateB

2017-03-02 1 2017-03-01 is DateA between DateB and DateB+1day? YES

2018-06-03 2 2018-06-03 is DateA between DateB and DateB+1day? YES

2019-05-13 3 2014-05-28 is DateA between DateB and DateB+1day? NO

2017-08-23 4 2015-08-23 is DateA between DateB and DateB+1day? NO

So the result of the count of rows, in this case, should be 2 (for Id=1 and Id=2)

Thanks!

JAVI

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Nathaniel_C — Sun, 10 Nov 2019 16:31:54 GMT

Hi Anonymous
2018-06-03 2 2018-06-03 is DateA between DateB and DateB+1day? YES

So which two date is DateA between on table B?

Nathaniel

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sun, 10 Nov 2019 16:44:46 GMT

Hi Nathaniel_C

Table A TableB

DateA ID DateB

2017-03-02 1 2017-03-01 is DateA between DateB and DateB+1day? YES

2017-03-02(DateA) is between 2017-03-01 (DateB) and 2017-03-02(DateB +1)

Between includes the values.

Javi

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Nathaniel_C — Sun, 10 Nov 2019 17:00:44 GMT

Hi Anonymous ,
So another way to say this is the date in table A for each ID must look at the same ID in table B, and be equal to the date in Table B, or Table B+1 for a yes. So it must match one of two dates only.
Nathaniel

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sun, 10 Nov 2019 17:10:48 GMT

Yes, that's more or less what I need but instead of "So it must match one of two dates only", the date A must be between the period.

In the example the condition is:

DateA between DateB and DateB+1day

but it can also be:

DateA between DateB and DateB+7day

So in this second case, the row must be counted is DateA is in any of the days od the period.

Thanks,

Javi

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Nathaniel_C — Sun, 10 Nov 2019 17:45:45 GMT

Hi Anonymous ,
I believe this is what you are looking for in the outcome.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Calculated Column

Between = VAR _curDate = ( TableA[Date] ) VAR _curID = TableA[ID] VAR _curTableBDate = CALCULATE ( MAX ( TableB[Date] ), FILTER ( TableB, TableB[ID] = _curID ) ) VAR _maxPerDate = _curTableBDate + 1 RETURN IF ( _curDate >= _curTableBDate && _curDate <= _maxPerDate, "Yes", "No" )

Measure for table in picture.

Between Dates2 = VAR _curDate = MAX ( TableA[Date] ) VAR _curID = MAX ( TableA[ID] ) VAR _curTableBDate = CALCULATE ( MAX ( TableB[Date] ), FILTER ( TableB, TableB[ID] = _curID ) ) VAR _maxPerDate = _curTableBDate + 1 RETURN IF ( _curDate >= _curTableBDate && _curDate <= _maxPerDate, "Yes", "No" )

Measure for Card in picture.

Sum of rows = CALCULATE(COUNTROWS(TableA),TableA[Between]="Yes")

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Anonymous — Sun, 10 Nov 2019 19:35:37 GMT

Thanks a lot Nathaniel_C

That was exactly what I was looking for.

😀

Re: Create a measure that counts values where a condition complies between columns of 2 tables.

Nathaniel_C — Sun, 10 Nov 2019 20:00:03 GMT

Hello, Anonymous
You are welcome! Glad we could get the logic straight.
You could create a variable and replace the 1 with it. Then it would be easy to change that at the beginning of the code to 7 or whatever.
Nathaniel