Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

firstnonblank issue

Hello,    Sorry for the question, I know it's a bit basic, but I'm a bit blocked...   i have this measure :   CALCULATE ( FIRSTNONBLANK ( INC[U_END], 1 ), FILTER ( 'INC', ...
Need Help
  • v-xicai's avatar
    v-xicai
    7 years ago

    Hi Anonymous ,

     

    If there are Unique IDs for [PROBLEM_ID] or [NUMBER_ID], this error message will be thrown. So the LOOKUPVALUE function is not suitable for this scenario. While the FIRSTNONBLANK function in the scenario which is equivalent to the LOOKUPVALUE will be Ok. If you need to show the blank value, you can create column but not a measure using the DAX below.

     

    Column 1= CALCULATE ( FIRSTNONBLANK ( 'INC_PROD'[CAUSED_BY], 1 ), FILTER ( 'INC_PROD', 'INC_PROD'[PROBLEM_ID]= 'PROB_PROD'[NUMBER_ID]))

     

    Best Regards,

    Amy

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello, 

 

I have a two tables:

 

Table A (number_id, prob_id, caused)

 

Table B (number_id)

 

Table A (prob_id) = Table B (number_id) 

 

what i want is to put in the Table B the column caused from Table A, and if the column caused have empty values i want the empty field in Table B to.

 

Can you help me ?

 

i can´t share my data, sorry for that

 

Best Regards,

JO

 

v-xicai's avatar
v-xicai
Community Support
7 years ago

Hi Anonymous ,

 

>>what i want is to put in the Table B the column caused from Table A, and if the column caused have empty values i want the empty field in Table B to.

 

1.Woud you like to create a column or a measure?

2.When the Table A[Caused] have empty value, the Table B 's new created column or measure will be empty too, right? I am not sure what desired result would you want, could you please share your sample data or desired output in screenshots for further analysis? Do mask sensitive data before uploading.

 

Best Regards,

Amy

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, 

     

    Thanks for your quick reply :smileyhappy:

     

    sorry again, but i can´t show any data, because are sensitive and privileged information 

     

    But what i want is show in a table the number_id from Table B and the cause that is related (Table A caused) to that number_id (if there is no information I want the field to appear blank).

     

    it is possible to this in a measure ? if it is, i perfer in a measure, but if is not possible i can create a new colomun in table B is not a problem.

     

    Thank you again for your help, very grateful :smileyhappy:

     

    Best Regards, 

    JO

    • v-xicai's avatar
      v-xicai
      Community Support
      7 years ago

      Hi Anonymous ,

       

      So your demand is lookup the matched TableA[prob_id] based on the filter TableA[prob_id]=TableB[number_id], if not exist , return the blank, right? If yes, you can try the measure below.

       

      Measure1 = IF(ISBLANK(LOOKUPVALUE(TableA[caused],TableA[prob_id],MAX(TableB[number_id]))),

      BLANK(),

      LOOKUPVALUE(TableA[caused],TableA[prob_id],MAX(TableB[number_id])))

       

      Best Regards,

      Amy

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi v-xicai , 

         

        first of all, thank you very much again for your help :smileyhappy:

         

        i try the following measure:

         

        IF(ISBLANK(LOOKUPVALUE(INC_PROD[CAUSED_BY], INC_PROD[PROBLEM_ID], MAX(PROB_PROD[NUMBER_ID]))), 
                        BLANK(), 
                        LOOKUPVALUE(INC_PROD[CAUSED_BY], INC_PROD[PROBLEM_ID], MAX(PROB_PROD[NUMBER_ID]))
)

        but show me a error :smileysad: : "A Table of multiple values was supplied where a single value was expected"

         

        I can not understand what I'm doing wrong because each inc_prod (number_id) may or may not have a prob_id and may or may not have an inc_prod (caused_by), but when it has a value associated with prob_id this prob_id is the id of the table prob_prod (number_id).

        Your formula to me makes perfect sense, but I can not understand where I'm failing.

         

        Sorry for the inconvenience, do you have any idea how to solve this? 

         

        Best Regards,

        JO