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

Impactful Individual

## See how many times something appears in the same table, but with a different value & later date.

```Acceptor ID	Mid		Datetime	   (New Column or Measure)
38084084	1b2c	        1/1/2017	    2
8058085		3jf8		1/1/2017	    0
8082482		2j2j		1/2/2017	    1
802919088	1b2c		1/2/2017	    0
802919088	1b2c		1/3/2017	    0
98208		2j2j		1/3/2017	    0
8058085		3jf8		1/3/2017	    038084084        1b2c            1/4/2017            0```

Sorry for the terrible title.

So say I had the above table.  The 4th column shows what I'm looking to calculate - which is for each line to check the MiD, then find the number of times that MiD is found again in the table, but with a different Acceptor ID, and a later DateTime.

I feel like this is a case for VAR, but....how would I go about it?

1 ACCEPTED SOLUTION
Impactful Individual

Hi,

I must have poorly phrased what I was trying to do - looking at your code / learning what EARLIER did was what I really needed.

Seperately - even though I was thinking VAR, it turns out that wasn't needed per se- though, I kept it, becuase that is a really neat trick for replacing blank values with 0s (I would have otherwise left it, or done a really long IF statement).  Thanks for that as well!

```Calculation =

VAR number =
calculate(COUNTROWS(mytable),
FILTER(mytable,
mytable[Mid] = EARLIER(mytable[Mid])
&& mytable[Acceptor ID] <> EARLIER(mytable[Acceptor ID])
&& mytable[Datetime] > EARLIER(mytable[Datetime])))

RETURN IF(ISBLANK(number),0,number)```

The above returns the expected values.  I think because I phrased things poorly, you ended up answering an even harder question!

6 REPLIES 6
Impactful Individual

So here it is in Excel (which is ridiciously easy to do this in, vs. Power BI...)

EDIT:  OK I kinda understand VAR/EARLIER now, let me take another look at this tomorrow when I have access to the file.

Community Champion

Hi @mmace1

Please could you send me your Excel File or paste some extended data with expected results.

I will try to replicate this in Power BI

Regards
Zubair

Please try my custom visuals
Impactful Individual

Hi,

I must have poorly phrased what I was trying to do - looking at your code / learning what EARLIER did was what I really needed.

Seperately - even though I was thinking VAR, it turns out that wasn't needed per se- though, I kept it, becuase that is a really neat trick for replacing blank values with 0s (I would have otherwise left it, or done a really long IF statement).  Thanks for that as well!

```Calculation =

VAR number =
calculate(COUNTROWS(mytable),
FILTER(mytable,
mytable[Mid] = EARLIER(mytable[Mid])
&& mytable[Acceptor ID] <> EARLIER(mytable[Acceptor ID])
&& mytable[Datetime] > EARLIER(mytable[Datetime])))

RETURN IF(ISBLANK(number),0,number)```

The above returns the expected values.  I think because I phrased things poorly, you ended up answering an even harder question!

Community Champion

Hi @mmace1

Try this calculated Column

```Column =
VAR mytable =
FILTER ( TableName, TableName[Datetime] < EARLIER ( TableName[Datetime] ) )
VAR Result =
CALCULATE (
COUNTROWS ( TableName ),
FILTER (
TableName,
TableName[Mid] = EARLIER ( TableName[Mid] )
&& TableName[Datetime] > EARLIER ( TableName[Datetime] )
&& TableName[Acceptor ID] <> EARLIER ( TableName[Acceptor ID] )
&& NOT ( CONTAINS ( mytable, [Acceptor ID], TableName[Acceptor ID] ) )
)
)
RETURN
IF ( ISBLANK ( Result ), 0, Result )```

Regards
Zubair

Please try my custom visuals
Impactful Individual

Thanks!  So it seems to sort-of work.  For instance, on this one, I've limited it to all the entries for one MiD, then arranged the entries by the Datetime (SettlementDate).  The calculated column before the arrow are correct, but everything afterward is 0, when most of them should have a number as there are subsequent Acceptors IDs that don't match.

?

Community Champion

@mmace1

It works with the sample data ... But I am not sure about full dataset

Regards
Zubair

Please try my custom visuals

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.