equivalent of tableau Function: Lookup and first

[adnanarain]

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

[v-lili6-msft]

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

[adnanarain]

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.

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

Rank example

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[parry2k]

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

[adnanarain]

@parry2k  i tried to explain in above message please have a look.

[adnanarain]

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

[v-lili6-msft · Accepted 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

[adnanarain]

Thank you so much for the help