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
ckodadek
Helper I
Helper I

Dynamic TextBox

Hi,

I would like to be able to show a single cell's text inside of a text box.
Example: Insert text box, then the field in the textbox would take in my column, and filter out all blank values (leaving only the desired cell), thus creating a title that changes when the cell referenced changes.

The reason for this is to create a dynamic title. [If I can otherwise accomplish a dynamic title represented by cell A1, then please let me know]

 

Any advice on how to accomplish this?

1 ACCEPTED SOLUTION
wonga
Continued Contributor
Continued Contributor

@ckodadek

 

In reference to your original post, you wanted to use FIRSTNONBLANK right? If there will only be one value in a column at any time, then you can probably just use the DAX measure:

 

Title = FIRSTNONBLANK('Table1'[Column_1],'Table1'[Column_1]).

 

I used that with sample data where there was only one column with one value in a cell out of 15 cells. I was able to get the value in that cell into a card visual. Let me know if that's what you wanted, thanks.

View solution in original post

7 REPLIES 7
sornavoor
Resolver I
Resolver I

1)  define a DAX text measure

2 ) Use a Card Visual displaying the text dax meaure created in nstep 1

 

Please  see Will thompson's  MS Data Summit Video  in Youtube for example.Create Impactful Reports With Power BI Desktop

around  28th minute mark onwards.

@sornavoor @wonga I want to reference a precise cell. Example: 'table'[column][row]
If there is an easy way to do this is DAX, please let me know.


Using ALLNOBLANKROW when my column only has 1 non-blank cell, I'd expect the nonblank cell to be the value represented in the card.

I've tried "Measure = ALLNOBLANKROW('table'[column])" but this does not yield proper results. It throws an error: 

Error Message:

MdxScript(Model) (1, 43) Calculation error in measure 'electricCommander'[Title]: A table of multiple values was supplied where a single value was expected.

Stack Trace:

 

Invocation Stack Trace:

 

Activity ID
ef9ead0c-df36-346e-67b8-1a90eb34e514

Time
Wed Jun 01 2016 10:37:23 GMT-0400 (Eastern Daylight Time)

Version
2.34.4372.501 (PBIDesktop)

 

If I just want to have a single cell as the title, how would I set that up?
"Title = FIRSTNONBLANK('sheetName'[columnName], "")" ?

I tried that but it outputs a number.
Basically, the column is completely empty with the exception of the cell that needs to be the title.

wonga
Continued Contributor
Continued Contributor

@ckodadek

 

Can you provide some sample data and the expected result to provide some more clarity?

@wonga Sample Data: Table 'mySheet' contains Column "1Name" that has a string called "Title" in Row #1.
I want the word "Title" to appear in a text box.

The word "Title" is being extracted from a list of strings. So at one time it is "Title", the next time it is "Title B" the next time it is "Title C"...etc. and 'mySheet[1Name]Row#1 updates in the excel sheet.

All I need is something that will reference 'mySheet'[1Name]Row#1 from my data set (which is already loaded in) and set the string equal to whatever appears in 'mySheet'[1Name]Row#1.

wonga
Continued Contributor
Continued Contributor

@ckodadek

 

In reference to your original post, you wanted to use FIRSTNONBLANK right? If there will only be one value in a column at any time, then you can probably just use the DAX measure:

 

Title = FIRSTNONBLANK('Table1'[Column_1],'Table1'[Column_1]).

 

I used that with sample data where there was only one column with one value in a cell out of 15 cells. I was able to get the value in that cell into a card visual. Let me know if that's what you wanted, thanks.

@wonga Thanks. I'll accept this as the solution, as it produces the correct results.
I happened to find another method of doing it, listed below.

Title = CONCATENATEX(VALUES('sheetName'[columnName]),sheetName[columnName],"")

For another example:
https://blog.crossjoin.co.uk/category/power-bi-desktop/

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.