Best data modeling approach

Hi , PBI-Enthusiast 

According to your description, you want to get this table . Rught?

For your needs, your Service A belongs to one end table, and the rest belongs to the fact table. I don't recommend that you use many-to-many relationships to maintain your inter-table relationships.

I use the following table relationship:

I create the 'Service A' table in Power Query Editor:

let
    Source = Table.Distinct(Table.SelectColumns(#"Fact Table" ,{ "Key A","Service A","Service A Description" }))
in
    Source

And we need to unpivot the fact table :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZHPCsIwDMZfZfQ8IUmzWr2tTyB4HDv476qgB6dPbxPr5sApQj8oX1Lo72ubpjGIaEqzrgHEN6pYIFnZDs/tqyoW4JYAxawgiP6hr+NaHc6X07FAbdtyMoIrlyIoeicSoldiHzBu3/hyKHzjz/0i8eUpN9FwY/SjB7zaMPDtL76rOPHF76L/foj7HyKilCC+VWUewkREtiFYaxNffKfKOQRmTnzxvSrjENoH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Key A" = _t, #"Service A" = _t, #"Service A Description" = _t, #"Key B" = _t, #"Service B" = _t, #"Service B Description" = _t, #"Service Time" = _t, #"Support Time" = _t, Classification = _t, Responsible = _t, Criticality = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key A", Int64.Type}, {"Service A", type text}, {"Service A Description", type text}, {"Key B", Int64.Type}, {"Service B", type text}, {"Service B Description", type text}, {"Service Time", type text}, {"Support Time", type text}, {"Classification", type text}, {"Responsible", type text}, {"Criticality", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Key A", "Service A", "Service A Description", "Key B", "Service B", "Service B Description"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then we apply the data to Power Bi Desktop ,here are the steps in Power Bi Desktop you can refer to :

(1)We need to click "New Column" to  create a calculated column in 'Fact Table':

Group = IF([Attribute] in {"Service Time","Support Time"} , "Group A" , IF([Attribute] in {"Classification","Responsible"} , "Group B" , "Group C"))

(2)Then we need to add a hirearchy in this table:

We can click the three dots in the right of the 'Fact Table'[Group] and click 'Create hirearchy' and then add the [Attribute] to this hirearchy.

(3)Then we can create a measure:

Measure = IF( HASONEVALUE('Fact Table'[Service B]) , MAX('Fact Table'[Value]))

 (4)Then we put the field we need on the Matrix visual and then we can get this:

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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