DAX - Return only one matching row?

[Anonymous]

This is kind of an odd request.

I have two tables, a fact table and a pivoted lookup table.

I have a [LookupKey] in my fact table that can have duplicate values. I have a similar [LookupKey] in the Pivoted Lookup table.

I need to be able to return a value from the pivoted lookup table to only the first or one of the matching rows in the fact table.

For example, my fact table can have two rows with the lookup key of "DogCat". The pivoted lookup table has a value of 1,500 for "DogCat". Is there a way I can return the 1,500 to just the first row or only one match in my fact table?

[v-xicai · Accepted solution]

Hi @Anonymous ,

You may create columns in 'fact table' like DAX below.

Rank = CALCULATE(COUNT('fact table'[LookupKey] ),FILTER(ALLSELECTED('fact table'),'fact table'[LookupKey] <=EARLIER('fact table'[LookupKey] )))
 

Matched value= IF('fact table'[Rank]=1, CALCULATE (FIRSTNONBLANK ('Pivoted Lookup table'[matched Value], 1 ),FILTER ( ALL ( 'Pivoted Lookup table'), 'Pivoted Lookup table'[LookupKey] ='fact table'[LookupKey] )), BLANK() )

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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.

Should be able to use something like FIRSTNONBLANK and FILTER to achieve what you need or perhaps MAXX or MINX.