Firstnonblank is not working right

[Anonymous]

Hi everyone.

The structure I have is pretty complex and maybe that's the reason why the formula doesn't work right.

So, I have a calculated table 'Customers calc'. It is mainly based on NATURALLEFTOUTERJOIN(customers,subscriptions). This table has a service_code column by which I'm trying to get a case_study_link from another table. Since one service_code can have multiple case_study_links, I'm using FIRSTNONBLANK.

The calculated column is the following:

Case study = 

CALCULATE (

FIRSTNONBLANK( case_studies[case_study_link], 1 ),

FILTER ( ALL ( case_studies ), case_studies[service_code] = 'Customers calc'[service_code] ))

And it does return a right case_study_link in 32 cases, while I have more than 100 values. So in some cases, it returns blank instead of the value, while in others it works fine. I don't see any regularity here...

I'm attaching a screenshot of the model with the abovementioned tables.

I would appreciate any help.

[v-xulin-mstf]

Hi @Anonymous

Could you provide pbix file after removing sensitive information?

Sample data and expected output would help tremendously.
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

Best Regards,

Link

[amitchandak]

@Anonymous , Try a formula like this

MINX(FILTER ( case_studies, case_studies[service_code] = 'Customers calc'[service_code] ), case_studies[case_study_link])

[Anonymous]

Hi @amitchandak 

With this formula, I get 66 values which is better than before. But some values are still missing. For example, I noticed that it returns blank instead of a value when there are 3 values in the case_studies table for a service code.