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
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?
Solved! Go to Solution.
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!
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.
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
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!
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 )
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.
?
It works with the sample data ... But I am not sure about full dataset
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
108 | |
104 | |
95 | |
70 |
User | Count |
---|---|
173 | |
134 | |
132 | |
101 | |
95 |