Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Firstnonblank is not working right

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.
 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a formula like this

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.