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

Get values if another column's values match the column names used in visualisation

Hi everyone. Apologies for the poor title, I'm not quite sure how I could describe it.

 

I have 2 tables in my model. One has hundreds of numerical columns:

UnchangedData.jpg

 

The other one has 3 columns designed to remap the column names in the table above and assign a measuring unit to each column (energy meter data).

TomBLG_1-1643905558313.png

 

I've successfully converted the column names:

ChangedData.jpg

 

I'm including some of these columns in a table and I'd like to have it show as: Column Name, Value and the Unit assigned to that meter.

 

Through a matrix table I've gotten the first two and in red you can see the desired outcome:

DesiredOutcome.jpg

 

I believe I need to create a Measure to give me a list of Units based on these values, the problem is that I'm not filtering anything, these columns are being dragged and dropped in the visualisation.

 

Unpivotting the main data and then creating a relationship between the column names isn't an option due to the length and other constraints on the real dataset.

 

I've put the dummy model used here:

https://drive.google.com/file/d/1UzI-PZFtrRbbjWpnhRRxEbGsiMy4RVjV/view?usp=sharing

 

Any help would be appreciated,

Thank you

2 ACCEPTED SOLUTIONS
v-xiaotang
Community Support
Community Support

Hi @TomBLG 

vxiaotang_0-1644460057788.png

 

In the picture you add the Unit to the right of the Value column, however, I am afraid of it is not possible.

The reason is (see bellow) if you put measure into the visual, then it will add a line at the bottom instead of a column on the right.

vxiaotang_1-1644460445731.png

Best Regards,

Community Support Team _Tang

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

View solution in original post

Hi Xiaotang,

 

I agree with you. Without unpivotting the main data to then create a relationship between Unpivotted Attribute and FieldName, it doesn't appear to be doable. I'll be marking this as the solution since it's not a possible request.

 

I ended up creating a custom column to include the Units in brackets after the field name and this is working just fine.

View solution in original post

6 REPLIES 6
v-xiaotang
Community Support
Community Support

Hi @TomBLG 

vxiaotang_0-1644460057788.png

 

In the picture you add the Unit to the right of the Value column, however, I am afraid of it is not possible.

The reason is (see bellow) if you put measure into the visual, then it will add a line at the bottom instead of a column on the right.

vxiaotang_1-1644460445731.png

Best Regards,

Community Support Team _Tang

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

Hi Xiaotang,

 

I agree with you. Without unpivotting the main data to then create a relationship between Unpivotted Attribute and FieldName, it doesn't appear to be doable. I'll be marking this as the solution since it's not a possible request.

 

I ended up creating a custom column to include the Units in brackets after the field name and this is working just fine.

lbendlin
Super User
Super User

Sadly, Power BI Desktop does not support the EVALUATE DAX function.

 

Can we assume that the UnchangedColumns are the dimension attributes and the renamed columns are the facts?  In that case you can unpivot your table, and then blend in the unit of measurement.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NVbJkeQwDMtl3vMQKV6KZWvyT2MhQH6525Z4AQT579+PLzs/vz9Vcx9mx2zwo0+24zl54j5t40DrxPtRXfdLm99HnTyF51mx7ucdaxLPjMj7PrubHvzkuh9szV73i8Vpu1dyT1zf5THr+j4n9zWFG3Gfs4exrLI2/IjddS/GGb//d/a17F2KsHOHHNS1M3Py/j/u/G++ze6F6sihPRt+2NZ1Iwh4YohrP9c4oSxggq6HBwYG6HIjPV5YwxB9MhZjWCr02GFhdyJ9BROqa2fQOUyde+L00FcWq4MUnQXe5Qwa1bvHPNa5hnoDEGa/lLVbz30RwFRJTCmHDGJst96sYy/WD0Ge1BUnqB1dJd8s0HTnfZ8WRp8TLfZ4b5EgmW0Xk93TPrLD3MomTCQAloR6HULbKXaE1wtyRfFLGflkEXvTd6twk1mMyYPlipoeFtYedjuTBc60n7/fS3XnB1sMZgN1FjKNhIypfd8jAsUCdvFg5KHl2eABgaFDH+ZmZazZ+VK4AF3zPmRxZ4ltK3weHU+RGwBKkQNzIpdbQKFZdBKxCSlf5FciWzZZbnXCThrvHNpGpGyBnWtGB2qZWmQRkd6b/9GX90KALvRp4jPIY4+vh8kDWKW5ax8hs5XX9lS+ZydvzjE2Uwq4SRdOhy0xNuQxsF9MN0FoMT+n1Dxo4CWmRjCqHa2+avUEeMcWhm9iVmOKbsE9o+q9Rl30Egt0Gpu7FuGOdj7RH4wfDFV7RAzJ23EkiREWytyWOqqHV/Y6rPKcagW+rJXrqPc619MN4VEZPICbhBhMaIXpyY4o36waogzJaZECoAZZDhFlBx5wmF2wklIVoB/Z1R/gfrv90d3YL48r93+IiARebAeznLrU21xoRElnQnVbi3qDzxIqpNAqmLskzEzyAVX2/UpNm0NlqiPBR6iMdfqQANBY5gYrLkTCWTakmgoZXGNnTIvFYAhvXKG5FuDysMeL1YMGUtCgDSxzI4cjqm29PpQjAQ0tZ9ww7gLHOMJqSsfQdK9XRiqnKDDIGOZBczK6LawgrMaCXIUVvFfPNFn8CO9eQdaBbaFyG6l+CXLLe6BmND5sDsggzyF0Eunsp+5rJQ8gqEeg4NC76sYDKPbRQU0kiB3xxFhYAtqS6eR5JtE5Gm6rcgsQd/UGNOsjtHgKFjiTviW1xxsGjvHPgJAoGXpHg3iDca2Ga0oQ5hovzJoH/8h2ax0AkUm4vd/isUV5u9SnJ/g+H9XFlztF+QMbw4jdavYFxaCcwRixRPovsxdeJzl7pks3YIQdh71GL5o6jxglRTUKr+wbF0+sUXLRAJ3z2TJpH4ogebrUWkq6tCBsgoAVheGcp8+YHYQCOLiS77e87HlO4rGypBSFSSDLdkLbUb1t6Yjmrr0AczQ0c6Fq34Yh7YdciZzuI/Ig8rf3aNJDlp9cjIh65VTNo6bGuFGZcrmYAy5x2uVTaKwI16Sv/iwW6ZHFi6gDw8bAKiWKCpA3s6QnjmK+pUHbxWMeaJXUtkGVSRRUkS7R6YRU280FQ5I2Q9eIkBGU1sIE6UXtvR6tXK2SLgXzrb3T/K0kGugGcy9CrTcOjBkIevzBr7Uae6P2x2OppVrKedn6BqCyx1hjb9S+u/Df338=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [UnchangedCol1 = _t, UnchangedCol2 = _t, UnchangedCol3 = _t, UnchangedCol4 = _t, Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, UnchangedCol5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, UnchangedCol6 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t, Column27 = _t, Column28 = _t, Column29 = _t, Column30 = _t, Column31 = _t, Column32 = _t, Column33 = _t, Column34 = _t, Column35 = _t, Column36 = _t, Column37 = _t, Column38 = _t, Column39 = _t, Column40 = _t, Column41 = _t, Column42 = _t, Column43 = _t, Column44 = _t, Column45 = _t, Column46 = _t, Column47 = _t, Column48 = _t, Column49 = _t, Column50 = _t, Column51 = _t, Column52 = _t, Column53 = _t, Column54 = _t, Column55 = _t, Column56 = _t, Column57 = _t, Column58 = _t, Column59 = _t, Column60 = _t, Column61 = _t, Column62 = _t, Column63 = _t, Column64 = _t, Column65 = _t, Column66 = _t, Column67 = _t, Column68 = _t, Column69 = _t, Column70 = _t, Column71 = _t, Column72 = _t, Column73 = _t, Column74 = _t, Column75 = _t, Column76 = _t, Column77 = _t, Column78 = _t, Column79 = _t, Column80 = _t, Column81 = _t, Column82 = _t, Column83 = _t, Column84 = _t, Column85 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"UnchangedCol1", Int64.Type}, {"UnchangedCol2", Int64.Type}, {"UnchangedCol3", Int64.Type}, {"UnchangedCol4", Int64.Type}, {"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"UnchangedCol5", Int64.Type}, {"Column6", Int64.Type}, {"Column7", Int64.Type}, {"Column8", Int64.Type}, {"Column9", Int64.Type}, {"Column10", Int64.Type}, {"Column11", Int64.Type}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"UnchangedCol6", Int64.Type}, {"Column14", Int64.Type}, {"Column15", Int64.Type}, {"Column16", Int64.Type}, {"Column17", Int64.Type}, {"Column18", Int64.Type}, {"Column19", Int64.Type}, {"Column20", Int64.Type}, {"Column21", Int64.Type}, {"Column22", Int64.Type}, {"Column23", Int64.Type}, {"Column24", Int64.Type}, {"Column25", Int64.Type}, {"Column26", Int64.Type}, {"Column27", Int64.Type}, {"Column28", Int64.Type}, {"Column29", Int64.Type}, {"Column30", Int64.Type}, {"Column31", Int64.Type}, {"Column32", Int64.Type}, {"Column33", Int64.Type}, {"Column34", Int64.Type}, {"Column35", Int64.Type}, {"Column36", Int64.Type}, {"Column37", Int64.Type}, {"Column38", Int64.Type}, {"Column39", Int64.Type}, {"Column40", Int64.Type}, {"Column41", Int64.Type}, {"Column42", Int64.Type}, {"Column43", Int64.Type}, {"Column44", Int64.Type}, {"Column45", Int64.Type}, {"Column46", Int64.Type}, {"Column47", Int64.Type}, {"Column48", Int64.Type}, {"Column49", Int64.Type}, {"Column50", Int64.Type}, {"Column51", Int64.Type}, {"Column52", Int64.Type}, {"Column53", Int64.Type}, {"Column54", Int64.Type}, {"Column55", Int64.Type}, {"Column56", Int64.Type}, {"Column57", Int64.Type}, {"Column58", Int64.Type}, {"Column59", Int64.Type}, {"Column60", Int64.Type}, {"Column61", Int64.Type}, {"Column62", Int64.Type}, {"Column63", Int64.Type}, {"Column64", Int64.Type}, {"Column65", Int64.Type}, {"Column66", Int64.Type}, {"Column67", Int64.Type}, {"Column68", Int64.Type}, {"Column69", Int64.Type}, {"Column70", Int64.Type}, {"Column71", Int64.Type}, {"Column72", Int64.Type}, {"Column73", Int64.Type}, {"Column74", Int64.Type}, {"Column75", Int64.Type}, {"Column76", Int64.Type}, {"Column77", Int64.Type}, {"Column78", Int64.Type}, {"Column79", Int64.Type}, {"Column80", Int64.Type}, {"Column81", Int64.Type}, {"Column82", Int64.Type}, {"Column83", Int64.Type}, {"Column84", Int64.Type}, {"Column85", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",Table.ToRows(Table.SelectColumns(FieldAttributes,{"SQL Name", "Description"}))),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"UnchangedCol1", "UnchangedCol2", "UnchangedCol3", "UnchangedCol4", "UnchangedCol5", "UnchangedCol6", "NewName1", "NewName2", "NewName3", "NewName4", "NewName5", "NewName6", "NewName7", "NewName8", "NewName9", "NewName10", "NewName11", "NewName12", "NewName13", "NewName14", "NewName15", "NewName16", "NewName17", "NewName18", "NewName19", "NewName20", "NewName21", "NewName22", "NewName23", "NewName24", "NewName25", "NewName26", "NewName27", "NewName28", "NewName29", "NewName30", "NewName31", "NewName32", "NewName33", "NewName34", "NewName35", "NewName36", "NewName37", "NewName38", "NewName39", "NewName40", "NewName41", "NewName42", "NewName43", "NewName44", "NewName45", "NewName46", "NewName47", "NewName48", "NewName49", "NewName50", "NewName51", "NewName52", "NewName53", "NewName54", "NewName55", "NewName56", "NewName57", "NewName58", "NewName59", "NewName60", "NewName61", "NewName62", "NewName63", "NewName64", "NewName65", "NewName66", "NewName67", "NewName68", "NewName69", "NewName70", "NewName71", "NewName72", "NewName73", "NewName74", "NewName75", "NewName76", "NewName77", "NewName78", "NewName79", "NewName80", "NewName81", "NewName82", "NewName83", "NewName84", "NewName85"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"UnchangedCol6", "UnchangedCol5", "UnchangedCol4", "UnchangedCol3", "UnchangedCol2", "UnchangedCol1"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, FieldAttributes, {"Description"}, "FieldAttributes", JoinKind.LeftOuter),
    #"Expanded FieldAttributes" = Table.ExpandTableColumn(#"Merged Queries", "FieldAttributes", {"Unit"}, {"FieldAttributes.Unit"})
in
    #"Expanded FieldAttributes"

 

Hi Ibendlin,

 

Both unchanged and changed columns are the same type of data. Some of them simply won't require a rename as they come off SQL with the name format we want.

 

I'm unable to unpivot my data as this is a small part of a much larger report, all of it running on the same model. On top of that, the real model has 3 similar tables, with thousands of rows and hundreds of columns. Unpivotting just one turns the file to 200mb due to its length, this is not an option I'm afraid.

 

For the time being, I've added another column to the table I'm using for renaming:

 

= Table.AddColumn(Source, "Name", each [Description]&" ("&[Unit]&")")

 

This provides me with the name I want and the unit in brackets. It's not ideal as I'll need to rename the field to remove the "(Unit)" part but short of a measure that I'm missing to get Unit done in a way through DAX, it may be the best alternative.

 

"Unpivoting just one turns the file to 200mb due to its length, this is not an option I'm afraid."

 

How is this a problem?  200MB is not that big.  Your real problem is the unfortunate format of your source data.

 

See attached - maybe that will change your mind.

 

We have few dozen reports, each with multiple pages and graphs built with the main dataset tables not being unpivotted. An enormous amount of work would need to be done in the graphs to have them display the same way in that format. For this exercise, I can't rely on unpivotting Data.

 

Thanks for having a look, appreciated.

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.