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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
backflash
Helper II
Helper II

Need a solution to find out referencial invoice (LOOKUPVALUE and FIRSTNONBLANK both not working)

Hi everybody,

 

I tried to solve the following problem with LOOKUPVALUE - but because I have multiple results, it's not working. I searched the forum for an alternative and found FIRSTNONBLANK, now the DAX seems to work fine but somehow I do not get any result (simply blanks).

 

My data model is as follows:

I have only one table. This table contains some details regarding invoices (all the invoice positions)

The invoice id is a column called Rid in my table and this is unique for each invoice, but as we have positions in this table, it can be dosplayed multiple times (not unique in the table).

There is another column that indiciates if there is another invoice that has been invalid due to this invoice. This is the RefRID column. It is also not unique in my table because I have invoice positions.

 

If an invoice has been made invalide (due to a new one): I want to know for each invoice by which invoice it has become invalid.

 

To indicate, if an invoice has been invalid because of another invoice that is referenced to it, I use this code:

 
IF(CONTAINS(clxT_Invoice, clxT_Invoice[RefRid], clxT_Invoice[Rid]), 1, 0)
 
 This is working fine.
 
But when I try to now grab the corresponding invoice id, it doesn't work.
I used a LOOKUPVALUE-statement, which cannot work because I have some duplicated Rids.
 
So I tried this, but I do not get any results.
CALCULATE(FIRSTNONBLANK(clxT_Invoice[Rid], 1), FILTER(ALL(clxT_Invoice), clxT_Invoice[RkId] = clxT_Invoice[RefRkId]))

 

Please see some rows of my table as an example:

backflash_0-1680721557148.png

In the rows you can see one common case:

Invoice 20220538 has been replaced by invoice 20220584

backflash_2-1680721859223.png

 

 

Invoice with Rid 20220586 has replaced the invoice with id 20220584

backflash_1-1680721713281.png

 

that means also, that invoice 20220584 itself has been replaced by the invoice 20220586 and this is what I would like to have in the column here:

 

 

backflash_3-1680722074260.png

 

 

I really thought this would be an easy one - but since I do not have different tables I can do a lookup on but have to consider duplicated values due to the table containing invoice positions I am a little bit confused....

 

Do I need to somehow make a DAX containing an order of the rows in order to make that work?

 

Hope someone has an idea.

 

Thanks in advance

 

Regards

Vanessa

 

 

1 REPLY 1
lbendlin
Super User
Super User

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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