topic Re: Extract and display text from dim table based on what is on fact table in DAX Commands and Tips

Extract and display text from dim table based on what is on fact table

matrix_user — Mon, 13 Jun 2022 21:12:43 GMT

Hello I would like help on creating a calculated column to display addresses from a dim table based on the post code in a fact table. I already have the two tables related via postcodes.

So, the addresses in green below are displayed in a new created calculated column.

Thanks in advance,

Re: Extract and display text from dim table based on what is on fact table

Samarth_18 — Tue, 14 Jun 2022 05:48:25 GMT

Hi matrix_user ,

Sorry If I misunderstood your question,

Why do you need to refer fact table when you have everything available in dimention to create a address column. You could directly create a column as below:-

Address = [Postcode]&" "&[Locality]&" "&[State]&" "&[Country]

Re: Extract and display text from dim table based on what is on fact table

matrix_user — Tue, 14 Jun 2022 10:01:50 GMT

Hello Samarth_18

Thank you for your reply. I may have not illustrated it properly. A further illustration is attached below. My fact table gets updated with new postcodes only, (no Locality, or State). The dim table is somewhat a complete reference of all the postcode of Australia with Locaility, States, Country and other data such as Latitude and Longitude coordinates.

So to create addresses in the fact table, I was thinking of linking it to the dim table so everytime there is a refresh, the fact table will be assigned a complete address from the dim table.

From there, I can use the map visuals and drag the addresses field.

Re: Extract and display text from dim table based on what is on fact table

Samarth_18 — Tue, 14 Jun 2022 10:08:28 GMT

matrix_user , I would suggest you to create a column in fact table as below:-

Address = RELATED ( dim_table[Postcode] ) & " " & RELATED ( dim_table[Locality] ) & " " & RELATED ( dim_table[State] ) & " " & RELATED ( dim_table[Country] )

Re: Extract and display text from dim table based on what is on fact table

matrix_user — Tue, 14 Jun 2022 10:44:44 GMT

Yes I tried that but even though I related postcodes between the fact and dim table, Power BI seems to say the " the Locality [Postcode] either doesn't exist or doesn't have a relationship to any table available in the current context" When I managed the relationship, I could only use the many to many cardinality and cross filter both.

Re: Extract and display text from dim table based on what is on fact table

Anonymous — Thu, 16 Jun 2022 05:52:49 GMT

Hi matrix_user ,

The problem is that when the Post Code is used as a key in the dimension table, its value should be unique.

For example, we create a calculated column in the fact table. When the calculated column is iterated in the first row of the fact table, go from the fact table to the dim table to find the record with postcode="0200", but the dim table has two such records. , so it doesn't know which record to return.

It is recommended that you consider rebuilding the dim table so that the dim table is on the "1" side of the relationship.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

Re: Extract and display text from dim table based on what is on fact table

matrix_user — Thu, 16 Jun 2022 19:36:29 GMT

Thank you both Samarth_18 and Gao