Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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.

 

Thanks in advance

1 ACCEPTED SOLUTION

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




Between dates.PNG

 





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

Proud to be a Super User!




View solution in original post

10 REPLIES 10
Nathaniel_C
Super User
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
 


count dates.PNG

 


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

 

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

 

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

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

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




Between dates.PNG

 





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.

😀

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors