How to fix fact and dim table in power bi

[sumiteshkumar]

i have a table "table A" (fact table) in power bi that i created by extracting the string values from a "column 9" from "table B" and then unpivoting, new columns in "table A" are "column key","column name", "column unit", "column designation", here "column key" is the common that connects with other tables, also "column key" has duplicates after unpivot, when i use these columns in slicer it shows me duplicate values, so i tried using dim table to normalise the duplicate, but dim table doesnot contain all values that we have in fact table

i have created a dimension table 'DimValues' from fact table 'table A', but it does not show all the values in it

DimValues =
ADDCOLUMNS(
DISTINCT(
SELECTCOLUMNS(
'table A',
"column key", 'table A'[column key]
)
),
"column name", CALCULATE(MAX('table A'[column name])),
"column unit", CALCULATE(MAX('table A'[column unit])),
"column designation", CALCULATE(MAX('table A'[column designation]))
)

[Anonymous]

Hi @sumiteshkumar ,
You want to extract multiple associated values from a column key, and MAX() is only suitable for aggregate data scenarios, and it can only return a single maximum value. If a column key is associated with multiple values, MAX() will not retain all of the items in those multiple values. In our test case, if you want to use DAX to return multiple values, it will be difficult, most of the functions will only return a single value, I recommend you to go to Power query to eliminate duplicates.

I hope this solves your problem, if you have any further questions you can contact me at any time, I will get back to you as soon as I receive your message!
I look forward to your reply!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[sumiteshkumar]

@Anonymous thanks for you reply, so should i remove duplicates from "column name", "column unit", "column designation" in the fact table, or just "column key" duplicates in fact table?

[Anonymous]

Hi @sumiteshkumar ,
Thank you for your reply
For your question, whether to de-emphasize the column key or to de-emphasize all the data, it mainly depends on your data structure, whether the column key is related to other columns, the following are some examples, I hope it can help you understand:

Remove duplicates of Column Key:

We can see that if we only de-emphasize the column key, he will only return a single value, thus losing the other values. When your column key column must be unique, you can do this step to ensure that there are no duplicate values.

If these columns are used to describe the details of a column key (e.g., the same column key has multiple column names), consider keeping them in the dimension table. As we can see in the figure below.

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[sumiteshkumar]

@Anonymous when i using this in slicer, it worked but when i use the same column in side filter pane it shows duplicates.

[OktayPamuk80]

Can you try to replace blanks with "" in your query and take distinct at the end? I had similar issues getting values from one column, that sometimes blanks can ruin your datamode.

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.

[sumiteshkumar]

@OktayPamuk80 thanks for your reply, i tried doing same, didn't worked?

[OktayPamuk80]

Did you also remove the duplicates afterwards, as Tom shen suggested?

One more thing, having blanks in the "key" field may be seen as duplicates. Filter out nulls and blank values in the key fields. What is your data model relationship show? If it is correct, it should show 1:m 

Regards,

Oktay

Did I answer your question? Then please mark my post as the solution.

If I helped you, click on the Thumbs Up to give Kudos.