Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I am using the following tableau formula:
LOOKUP(count([Account_Number]),FIRST()) - LOOKUP(ZN(count([Account_Number])), 0)
I need to convert this formula in power bi.
Thanks in advance for help
Solved! Go to Solution.
hi @adnanarain
Just create two measure as below:
Difference =
var _lastrow=CALCULATE([Number of accounts],FILTER(ALL('Table'[Date Difference]),'Table'[Date Difference]=MAX('Table'[Date Difference])-1))
return
IF(ISBLANK(_lastrow),0,_lastrow-[Number of accounts])
% = var _lastrow=CALCULATE([Number of accounts],FILTER(ALL('Table'[Date Difference]),'Table'[Date Difference]=MAX('Table'[Date Difference])-1))
return
DIVIDE([Difference],_lastrow,0)
Result:
and here is sample pbix file, please try it.
Regards,
Lin
hi @adnanarain
There are some workaround to get it, but it depends on your data structure.
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Lin
I am attaching sample data and the desired output. with this data, I am using matrix visual to find number of accounts, Difference, and %. Number of accounts is a simple count([account Number]) and the difference for row 0 (i.e Date Difference Column) will always be 0 and Row 1 is Account numbers of Row 0 - account numbers of Row 1, in example Account numbers of Row 0 = 3 and Account numbers of Row 1 = 1 so the difference for Row 1 will be 3-1 = 2 and last I want to calculate % for Row 0 the % will be 0 and for Row 1 the formula for % is difference (i.e 2 for Row 1) divided by count of account number for Row 0 (i.e 3) so 2/3 = 67%.
Below is the sample data:
Date Difference | Account Number | Account Open date |
0 | 1 | 2013 |
0 | 2 | 2013 |
0 | 3 | 2014 |
0 | 2 | 2013 |
0 | 3 | 2014 |
0 | 3 | 2014 |
1 | 4 | 2014 |
1 | 5 | 2013 |
1 | 6 | 2014 |
2 | 7 | 2013 |
2 | 8 | 2014 |
2 | 9 | 2013 |
3 | 10 | 2013 |
3 | 11 | 2014 |
4 | 12 | 2013 |
4 | 13 | 2013 |
4 | 14 | 2014 |
4 | 15 | 2014 |
and below is the desired output, I tried to show for Year 2013 only:
2013 | |||
Date Difference | Count of account | Difference | % |
0 | 3 | 0 | 0% |
1 | 1 | 2 | 67% |
2 | 2 | -1 | -100% |
3 | 1 | 1 | 50% |
4 | 2 | -1 | -100% |
I tried to explain, apologies in advance if anything is not clear.
@adnanarain , What I got from it is that it is a row operation function, to move up and down in row by giving a number of first and last.
There is no equivalent to that.
Based on what you need you can a function like you can use time intelligence to get prior months/qtr or year. Or Rank to move up and down in incremental values.
Time intelligence example
Rank example
@adnanarain you need to explain what this function does, maybe a lot of us don't know what this function returns, better to put some sample data and expected output.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@parry2k @v-lili6-msft @amitchandak I am attaching sample data and the desired output. with this data, I am using matrix visual to find number of accounts, Difference, and %. Number of accounts is a simple count([account Number]) and the difference for row 0 (i.e Date Difference Column) will always be 0 and Row 1 is Account numbers of Row 0 - account numbers of Row 1, in example Account numbers of Row 0 = 3 and Account numbers of Row 1 = 1 so the difference for Row 1 will be 3-1 = 2 and last I want to calculate % for Row 0 the % will be 0 and for Row 1 the formula for % is difference (i.e 2 for Row 1) divided by count of account number for Row 0 (i.e 3) so 2/3 = 67%.
Below is the sample data:
Date Difference | Account Number | Account Open date |
0 | 1 | 2013 |
0 | 2 | 2013 |
0 | 3 | 2014 |
0 | 2 | 2013 |
0 | 3 | 2014 |
0 | 3 | 2014 |
1 | 4 | 2014 |
1 | 5 | 2013 |
1 | 6 | 2014 |
2 | 7 | 2013 |
2 | 8 | 2014 |
2 | 9 | 2013 |
3 | 10 | 2013 |
3 | 11 | 2014 |
4 | 12 | 2013 |
4 | 13 | 2013 |
4 | 14 | 2014 |
4 | 15 | 2014 |
and below is the desired output:
2013 | 2014 | |||||
Date Difference | Count of account | Difference | % | Count of account | Difference | % |
0 | 3 | 0 | 0% | 3 | 0 | 0% |
1 | 1 | 2 | 67% | 2 | 1 | 33% |
2 | 2 | -1 | -100% | 1 | 1 | 50% |
3 | 1 | 1 | 50% | 1 | 0 | 0% |
4 | 2 | -1 | -100% | 2 | -1 | -100% |
I tried to explain, apologies in advance if anything is not clear.
hi @adnanarain
Just create two measure as below:
Difference =
var _lastrow=CALCULATE([Number of accounts],FILTER(ALL('Table'[Date Difference]),'Table'[Date Difference]=MAX('Table'[Date Difference])-1))
return
IF(ISBLANK(_lastrow),0,_lastrow-[Number of accounts])
% = var _lastrow=CALCULATE([Number of accounts],FILTER(ALL('Table'[Date Difference]),'Table'[Date Difference]=MAX('Table'[Date Difference])-1))
return
DIVIDE([Difference],_lastrow,0)
Result:
and here is sample pbix file, please try it.
Regards,
Lin
Thank you so much for the help