Forum Discussion
FIRSTNONBLANK issue
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
6 Replies
Anonymous 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.⚡
Anonymous 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?Anonymous 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.⚡
Hi Anonymous
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.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.
