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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors