Dynamic Format wrt Data Model Content (Numerics and Units)

[StreytFeyz]

Hello everyone,

I am a novice user of Power BI with enough experience with visuals, but very little command of Query M language.

I look for an approach to dynamically update Format of data by using a conditional function, or a reference statement to update Text next to Numerical Values.

As an example, I want to merge the content of two columns/fields in a dataset. Former column contains various numerical values (be it whole or decimal), and the latter column contains the units corresponding to each row of data (grams, metres, km/hr, DegC, etc. in text). So far, the approaches that are available have been only static, which require

1) Clicking Model at left pane and writing down Custom Format in Properties

2) Clicking Data at left pane, selecting a column and then write the custom Format in the 'Column tools' tab (say ##,0 \metres)

However, there could be a more dynamic approach where one can reference a Table[Column1] as Number, and combine it with the Text content at Table[Column2]. I wish to apply this to visual charts, such as y-axis of Line Bars, or in a Matrix. Rather than resorting to Tooltips section or Card visual, can there be a more dynamic way to display text next to an axis/column of values in a Power BI dashboard?

I would appreciate your feedback,

StreytFeyz

[StreytFeyz]

Hi Ibendlin,

Pardon me for replying late to provide the data you needed to assist,

I will post the table first, and then a .pbix file in a shareable link to address my issue.

Variable	Value	Unit
Sound Pressure	24	dBA
Energy	60	Wh
Power	6	W
Sound Level (Fundamental)	20	dB
Sound Level (3rd Harmonic)	8	dB
Sound Level (5th Harmonic)	5	dB
Air Temperature	27	DegC

By using the simple table here, I have the whole numbers indicated in the 2nd column 'Value', and the units in text in the 3rd column named 'Unit'.

Units Bench PBI File 

The hyperlink I posted below should lead you to the link to download the .pbix. I will run down with snapshots the intended goal I want to reach.

When I want to draw a Line graph or a Bar chart, I wish to be able to dynamically display the units of the values in the y-axis according to the filtered selections. By using slicer, I select the Variables to display the values of the variables highlighted. Sound Level is one of them for the 3 I display ion the image.

Without changing the format for just one column, how can I feature the 'Table'[Unit] text in the axes or at the tip of the axes, without using Cards?

Regards,
StreytFeyz

[lbendlin]

Thank you for providing the details.  Please check the link, it currently asks for a Google login.

Are you familiar with the concept of Field Parameters?  It has recently been expanded (in the November 2022 Power BI Desktop version) to now allow values too (in addition to columns and measures). This might be something worth looking into.

Power BI November 2022 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

[StreytFeyz]

Dear Ibendlin,

I checked out the newest features you mentioned, while they look like nice additions, it does not apply to what I've been looking for. 

Here is the mediafire link for the file I intended to link in the previous post:

UnitsBench Mediafire Download Link 

What I look forward to doing is this with the Units column in a graph/chart. The xy chart is vehicle speed vs. timelapse in Date & Time:

Just display the texts along with a value on an axis that is not millions or billions. But for whichever variables I filter, the value that corresponds shall also include the name of the unit that is in the field/column that matches the other. Or at the very least feature a single unit name at the far end of an axis (be it x or y). Is this dynamically doable without Cards or pivoting individual variables into their own columns?

Although pivoting is the answer, it is not the approach I am aiming to do it with at the moment.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523