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

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

 

 

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

Hi Anonymous ,

 

Did you create relationship between the two tables first of all? Based on my test, it worked fine.

 

Best Regards,

Amy

  • v-xicai's avatar
    v-xicai
    Community Support
    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

    Hi v-xicai 

     

    I created the relation between each table but the relation is inactive because each table is linked to a calendar table

     

    Best Regards (and thank you again)

    JO 

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

    Hi Anonymous ,

     

    Try to delete relationship between INC_PROD table and calendar table, then change the Cross filter direction of all relationships from "Single" to "Both". This means for filtering purposes, these tables are treated as if they're a single table.

     

    Best Regards,

    Amy

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-xicai , 

     

    I can not disable the relationship because I have enough measures that are dependent on this relationship.

    Is there no other way to do this without having to disable the relationship between tables?

     

    Best Regards, 

    JO

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-xicai ,

     

    It work. Thank you very much and thanks for the explanation :smileyhappy:

     

    Best Regards, 

    JO