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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mmace1
Impactful Individual
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	    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? 

 

 

1 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!

 

View solution in original post

6 REPLIES 6
mmace1
Impactful Individual
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.  

 

 

 Excel.PNG

 

 

 

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

@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
Community Champion
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

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

Thingy.PNG

 

 

 

?

@mmace1

 

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

 

Seehowmanytimes.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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