Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX - Return only one matching row?

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] ...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

v-xicai's avatar
v-xicai
Icon for Community Support rankCommunity Support
6 years ago

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.