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

HASONEVALUE help

Hi Experts

 

I am using the following dax formula to find the value in column b based on the filter selection in column A. When i have mulitple values in column B it returns bank, i want to return the first value 

so

column A (Selected)

07898

Column B

Apple

Apple

so just return Apple as opposed to blank. Not sure how to use earlier in this if that applies.

Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),VALUES(njrew_h_prmry_outcm[CUPDETAILS]),"")
1 ACCEPTED SOLUTION

Use FIRSTNONBLANK instead of VALUES. 

Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")

VALUES returns a column of values, which can't be displayed as a single data point.  Using FIRSTNONBLANK gets the first value, depending on the current order of the data. 

 

 If you're expecting all of the CUPDETAILS values to be the same within the same CUPCATNO, I would use SELECTEDVALUE instead.  This way it doesn't just pick a value randomly based on whatever ordering currently happens to be in place.

Cup_h_prmry = SELECTEDVALUE(njrew_h_prmry_outcm[CUPDETAILS], BLANK())

You can also use MAX, MIN, or any other aggregation function to determine which value to pick out of the list.

View solution in original post

6 REPLIES 6
LaurentCouartou
Solution Supplier
Solution Supplier

In your formula, which is A and which is B?

Anonymous
Not applicable

Apologies..

Column A = 

njrew_h_prmry_outcm[CUPCATNO]) and 
 
Column B = 
njrew_h_prmry_outcm[CUPDETAILS]
 

Use FIRSTNONBLANK instead of VALUES. 

Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")

VALUES returns a column of values, which can't be displayed as a single data point.  Using FIRSTNONBLANK gets the first value, depending on the current order of the data. 

 

 If you're expecting all of the CUPDETAILS values to be the same within the same CUPCATNO, I would use SELECTEDVALUE instead.  This way it doesn't just pick a value randomly based on whatever ordering currently happens to be in place.

Cup_h_prmry = SELECTEDVALUE(njrew_h_prmry_outcm[CUPDETAILS], BLANK())

You can also use MAX, MIN, or any other aggregation function to determine which value to pick out of the list.

Anonymous
Not applicable

Hi 

 

i am getting an error on the firstnonblank formula??

Cup_h_prmry = if(HASONEVALUE(njrew_h_prmry_outcm[CUPCATNO]),FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS]),"")

Ah, my apologies. This is what I get for just writing in the forum and not testing it in PBI.  I forgot an argument for FIRSTNONBLANK. 

FIRSTNONBLANK(njrew_h_prmry_outcm[CUPDETAILS], 1)

I'm using 1 here as an always true option so that the function returns the first value it gets no matter what.  You could replace it with some expression that evaluates to a value when good and a blank when bad to be even more selective about which first value you choose.  

To return the first value, you can use

TOPN( 1, VALUES(njrew_h_prmry_outcm[CUPDETAILS]), 1234)

Note the last argument (1234) is where you would normally put your ordering expression. Here, I just chose an arbitrary constant value.

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.