Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
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.
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 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.
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/