cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Super User

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")``

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

Proud to be a Super User!

10 REPLIES 10
Super User

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())``

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

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

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

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

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

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

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

Proud to be a Super User!

Anonymous
Not applicable

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

Super User

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")``

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

Proud to be a Super User!

Anonymous
Not applicable

Thanks a lot Nathaniel_C

That was exactly what I was looking for.

😀

Super User

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

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

Proud to be a Super User!

Anonymous
Not applicable

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.