Forum Discussion

pallavi_r's avatar
pallavi_r
Icon for Super User rankSuper User
2 years ago
Solved

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 prov...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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 Zhou

     

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

     

     

     

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

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.