Forum Discussion
Modelling and filtering issue - value getting doubled
Dear Team,
Can any one please tell me how to do the modelling for these 2 tables, so I can filter based on Rating on Power BI and I should not get sum of value doubled up because of 2 rating provider.
Dimension Table
| Security ID | Security Type | Rating Provider | Rating |
| S1 | Long | ABC | A+ |
| S1 | Short | ABC | A+ |
| S1 | Long | XYZ | A+ |
| S1 | Short | XYZ | A+ |
Transaction Table
| Security ID | Security Type | Value |
| S1 | Long | 100 |
| S1 | Short | 150 |
Thanks,
Pallavi
Hi pallavi_r ,
I think you just need to add key columns in both tables and then create a relationship between them.
You can edit data model in Power BI Service. Find the semantic model in your workspace and then open data model.
Key Column = COMBINEVALUES("-",'Dimension Table'[Security ID],'Dimension Table'[Security Type])Key Column = COMBINEVALUES("-",'Transaction Table'[Security ID],'Transaction Table'[Security Type])Result is as below.
For reference:Edit data models in the Power BI service (preview) - Power BI | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
pallavi_r , Try below method
Establish relationships between the tables. Connect the Security ID and Security Type columns from the Dimension Table to the corresponding columns in the Transaction Table. This will create a many-to-one relationship from the Dimension Table to the Transaction Table.
Since the Dimension Table has multiple entries for each Security ID and Security Type due to different Rating Providers, you need to create a unique dimension table that consolidates these entries. You can do this by creating a new table using DAX:
UniqueDimensionTable =
DISTINCT(
SELECTCOLUMNS(
'Dimension Table',
"Security ID", 'Dimension Table'[Security ID],
"Security Type", 'Dimension Table'[Security Type],
"Rating", 'Dimension Table'[Rating]
)
)
Modify Relationships: Update the relationships to use the UniqueDimensionTable instead of the original Dimension Table. Connect the Security ID and Security Type columns from the UniqueDimensionTable to the corresponding columns in the Transaction Table.Create measures to calculate the sum of the Value. This measure will ensure that the sum is not doubled when filtering by Rating:
Total Value =
CALCULATE(
SUM('Transaction Table'[Value]),
TREATAS(
VALUES('UniqueDimensionTable'[Security ID]),
'Transaction Table'[Security ID]
),
TREATAS(
VALUES('UniqueDimensionTable'[Security Type]),Use the Total Value measure in your Power BI visuals. When you filter by Rating, the measure will correctly calculate the sum of the Value without doubling it.
Hi bhanu_gautam , thanks for your reply. Can I create this UniqueDimensionTable dax table in power bi service. it is ms fabric modelling panel I have to do the modelling. No desktop PBI :-(. Kindly suggest
Thanks,
Pallavi
Hi pallavi_r ,
I think you just need to add key columns in both tables and then create a relationship between them.
You can edit data model in Power BI Service. Find the semantic model in your workspace and then open data model.
Key Column = COMBINEVALUES("-",'Dimension Table'[Security ID],'Dimension Table'[Security Type])Key Column = COMBINEVALUES("-",'Transaction Table'[Security ID],'Transaction Table'[Security Type])Result is as below.
For reference:Edit data models in the Power BI service (preview) - Power BI | Microsoft Learn
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
