Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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',
        'INC'[PROB_ID] = PROB[NUMBER_ID]
    )
)

it work fine, but I now want to do the same logic for a column that always has a value or "false" or "true". The same measure but instead of the column U_END ... the column i want know.... dont have blank values ....

 

Can anyone help me ?

 

Best Regards,

JO

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
v-xicai
Community Support
Community Support

Hi @Anonymous ,

 

Would you like to create measure using the FIRSTNONBLANK ? Then try the following formula. Or could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

CALCULATE ( FIRSTNONBLANK ( INC[Boolean column], 1 ), FILTER ( 'INC', 'INC'[PROB_ID] = MAX(PROB[NUMBER_ID] )) )

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Anonymous
Not applicable

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

 

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
Not applicable

Hi, 

 

Thanks for your quick reply Smiley Happy

 

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 Smiley Happy

 

Best Regards, 

JO

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
Not applicable

Hi @v-xicai , 

 

first of all, thank you very much again for your help Smiley Happy

 

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 Smiley Sad : "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

 

 

Hi @Anonymous ,

 

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

 

Best Regards,

Amy

Anonymous
Not applicable

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 

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
Not applicable

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

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
Not applicable

Hi @v-xicai ,

 

It work. Thank you very much and thanks for the explanation Smiley Happy

 

Best Regards, 

JO

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.