FIRSTNONBLANK issue

[Pbi07]

I was getting a memory issue with FITSTNONBLANK function. Anyone faced a similar issue or is there a better approach to the below issue. 

I have 2 tables. 

Entry Code	Contract Name
T1040	EVERGREEN
A2002	LAKE HOME
63283	EVERGREEN

Plan - 

Entry Code	Name
T1040	Evergreen

I have a summary table created for additional calculations . Table is based on the main table for which an entry code do not exist in plan table. 

I have created a colum - ALT Code  to search for the contract name and call out the Entry code that exist for the similar contract name

The Expected result is below. This works fine in the model. But when i have a larger data, i am getting a memory error and i am not sure if i am doing this right or is there a better approach to do the search. I cannot do a Vlookup as there could be multiple entry code and i am picking only the first entry code.  

Entry Code	Contract Name	Alt Code
63283	EVERGREEN	T1040
A2002	LAKEHOME

Model is attached. Please note that the function is working fine in this model, but fails with a larger data with the error 

" There's no enough memory to comlete this operation" 

https://drive.google.com/file/d/103KSquQOzzbEQt7i3Sj5b95M9LDHNdtg/view?usp=sharing

[v-jingzhang]

Hi @Pbi07 

You may try below codes:

Sum_table 2 =
FILTER (
    SUMMARIZE ( 'Table', 'Table'[Entry Code], 'Table'[Contract Name] ),
    NOT ( 'Table'[Entry Code] IN VALUES ( Plan[Entry Code] ) )
)

Alt code =
CALCULATE (
    MIN ( 'Table'[Entry Code] ),
    VALUES ( 'Table'[Entry Code] ),
    'Table'[Contract Name] = EARLIER ( 'Sum_table 2'[Contract Name] ),
    'Table'[Entry Code] <> EARLIER ( 'Sum_table 2'[Entry Code] )
)

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[Pbi07]

Thanks @v-jingzhang 

I ended up using 2 columns and splitting the logic as any way i needed 1 of the column for another visual. Thanks for the suggestion.

[parry2k]

@Pbi07 not sure if I understood the problem, it should be as simple as that given the relationship

Alt Code = RELATED ( PlanTable[Entry Code] )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[m3tr01d]

@Pbi07 It is 1 to Many but you are using Bidirectionnal filter.

Do you really need a Bidirectional filter? You can have a lot of side effect and bad performance on your model.

How many rows are we talking in these tables?

[parry2k]

@Pbi07 since your sample data has very few rows, in your real model, how these tables are related? 1 to Many or Many to Many?

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Pbi07]

@parry2k 

It is 1 to Many relation for Plan -> Table