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

[mmace1]

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	    0
38084084        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? 

[mmace1]

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.  

 

[Zubair_Muhammad]

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

[mmace1 · Accepted solution]

@Zubair_Muhammad

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!

[Zubair_Muhammad]

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 )

[mmace1]

@Zubair_Muhammad

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. 

?

[Zubair_Muhammad]

@mmace1

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