Forum Discussion
TMDL - calculated column
Hi everyone,
With the PBI interface, I have created groupings of columns. When I go to the TMDL view , these groupings are not recognized, so I'm trying to calculate a column in TMDL. I tried to put an IF condition, as if this value is in the list, then enter value "Group 1", else blank. But the system says "cannot find name" for [Agent]
column 'Conducteur Groupe Clean' =
IF('DIM - Personnel'[Agent] IN {"AGENT 1","AGENT 2","AGENT 3"},"AGENTS POLY EXPLOIT",blank)
Even though, the column [Agent] is in the same table 'DIM - Personnel', it can't find it. I tried removing 'DIM - Personnel', it still doesn't work.
Please help, thank you
Hi VincentRRT66 ,
If you click apply are you getting an error or does it applies the TMDL script?
Asking this because sometimes the intelisense get's "confused" and gives error where there are none.
7 Replies
Hi VincentRRT66 ,
To generate columns in the TMDL view you have to have certain rules because it's not just adding the new column and it's done you need to fullfill all the metadata information:
This is an example of a column I have added
column 'TEST COLUMN' = IF('Currency'[CurrencyCode] in {"USD", "EUR"} , 1,BLANK()) formatString: 0 lineageTag: e8719f59-c1c4-4db3-bbe4-ce5d52c55499 summarizeBy: sum annotation SummarizationSetBy = AutomaticBe aware that the tabs are also important when you apply the TMDL script also you cannot just add this column wihout any other code you need to copy the table information complety to the TMDL editor so it has the context where it needs to be created
so this would be part of a bigger script similar to this:
createOrReplace table Currency lineageTag: 519e577c-f264-4e99-8f7a-15d8eeb80d04 column CurrencyCode dataType: string lineageTag: 9c2f7399-6a83-4f75-96ae-5351cc4246f3 summarizeBy: none sourceColumn: CurrencyCode annotation SummarizationSetBy = Automatic column CurrencyName dataType: string lineageTag: cdc28786-9cf1-47ac-9c86-e83896b667ce summarizeBy: none sourceColumn: CurrencyName annotation SummarizationSetBy = Automatic column GroupType dataType: string lineageTag: d464944d-7f39-4a08-b376-f07c179d79d2 summarizeBy: none sourceColumn: GroupType annotation SummarizationSetBy = Automatic column 'TEST COLUMN' = IF('Currency'[CurrencyCode] in {"USD", "EUR"} , 1,BLANK()) formatString: 0 lineageTag: e8719f59-c1c4-4db3-bbe4-ce5d52c55499 summarizeBy: sum annotation SummarizationSetBy = Automatic partition Currency = m mode: import source = let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bVFLbsMgEL0K8jqL9gi249TKrwjLUS0ri2kycpAoVGDSNnfqqkfoxTrYCZaibtCDx/swtG1SV/NkRiubG6XAEn5I9rM2KWpBuPDWxKOnjBPOrOylOzFuvD5Gbskbwkt4B40OWYOato8DldYhIfWut6Ak6PukPA18DhqO/7HbYJyf5OjrYTIuV0FYGt2xVViicqQ3L1vCG/yUB7Ll6Eyk8nJBuPqQzrGFBX2IcamoQlnboe4p8ia7dhF56GJcD0wMrrlRvz86XqibOgzT+s7D1xR6VT/zQa3M26u8J3kRynK0/hy4yqhJt+bjBBTeqzKxHn4ELnKYrEBQ8ZG7IjxyhxovHhWxGZX9Pt9mu/8D", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CurrencyCode = _t, CurrencyName = _t, GroupType = _t]) in Source annotation PBI_NavigationStepName = Navigation annotation PBI_ResultType = TableWhen using TMDL you always need to be aware of the context of the artificatct inside the semantic model so if you are creating a table you need to do it at model level, a column at table level, measures can be done alone but you need to refer the table where it sit, functions can be done alone.
Thank you,
I did fulfill the metadata and it's inside the DIM - Personnel table, so it does have the context. The column [Agent] is in the same table, a few lines above. The auto-completion of the IF_THEN_ELSE only proposes measures, but doesn't find the column [Agent] inside the same table. It's indeed very similar to your calculated currency column, the same logic.
Hi VincentRRT66 ,
If you click apply are you getting an error or does it applies the TMDL script?
Asking this because sometimes the intelisense get's "confused" and gives error where there are none.
Hi VincentRRT66,
Have you had a chance to review the solution we shared by MFelix,anilelmastasi? If the issue persists, feel free to reply so we can help further.
Thank you.
Hello VincentRRT66 ,
Woluld you change blank to BLANK()?
If this solved your issue, please mark it as the accepted solution. ✅
Thank for the answer, but no. I still get the error Cannot find [Agent].
