Find the sum in multiple columns of a table based on a reference from another table

[kkanda]

I am working with Power BI for the past few months and I have the following problem.

I have two tables - Outages , Facilities. 

Facilities table looks like this:

A

B

C

There are some 300 unique facilities in the above table.

Outages column looks like this:

Facility1	Duration1	Facility2	Duration2	Facility3	Duration3
A	30			C	22
B	28	A	12
C	10	A	16	B	15
				A	22
B	15	C	23
C	10	B	20
A	20	C	15	B	11
		B	18	C	20

There are some 3000 records in this table.

I established a 1 to Many relationship between Facilities table and each of the Facility columns  - 3 separate relationships from Facilities table to Outages table.

I have to find out for each Facility (in Facilities table), the sum of Outages from the Outages table in whichever column that Facility is listed along with the corresponding Outage.  For example, for Facility A, the sum of Outages will be 30+12+16+22+20 = 100. 

Any Facility is listed only once in one row (record). But it can be listed either in Facilities 1 or Facilities 2 or Facilities3 column. If a Facility is listed in the Outage table with a blank Duration, it has to be counted as 0.

I tried SUMX, SWITCH functions to pull the reference from Facilities table to Outages table, but it is not working. In fact, the Intellisense in DAX is not showing Facilities table.

Hope that I explained my problem clearly. Please suggest a solution how I go about creating Measures in either the Facilities table or Outages table to get the desired output. I want to list the Facilities with Total Outage in a Table or Chart in the dashboard.

Thanking you in advance.

[mahoneypat]

Unpivotting your data will make your analysis much easier.  Here is an example to see how to transform your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY1BCsAwCAS/EjznoIaWXNM8I/j/b1RFSwI9uIg7jGvBgAoNNUrM1GEGqQseW7uGQcQfZJ1hhNndGkbT5WXZfCWYzalU/Gn/Tn+MRzfyNFPgJjr++amnHEHkBQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Facility1 = _t, Duration1 = _t, Facility2 = _t, Duration2 = _t, Facility3 = _t, Duration3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Facility1", type text}, {"Duration1", Int64.Type}, {"Facility2", type text}, {"Duration2", Int64.Type}, {"Facility3", type text}, {"Duration3", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Attribute.1", "Attribute.2"}),
    #"Pivoted Column" = Table.Pivot(#"Split Column by Character Transition", List.Distinct(#"Split Column by Character Transition"[Attribute.1]), "Attribute.1", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Facility", "Duration"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Duration] <> null)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Facility", type text}, {"Duration", Int64.Type}})
in
    #"Changed Type1"

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[kkanda]

Pat, Thanks for the reply. I am not well versed in the M script... and I did not want to use it as I found the DAX solution much easier to follow. Thanks again for the response.

[jdbuchanan71 · Accepted solution]

Hello @kkanda 

You can use USERELATIONSHIP to change which join is considered in the measure.

Duration 1 = 
CALCULATE (
    SUM ( Outages[Duration1] ),
    USERELATIONSHIP ( Facilities[Facility], Outages[Facility1] )
)

Duration 2 = 
CALCULATE (
    SUM ( Outages[Duration2] ),
    USERELATIONSHIP ( Facilities[Facility], Outages[Facility2] )
)

Duration 3 = 
CALCULATE (
    SUM ( Outages[Duration3] ),
    USERELATIONSHIP ( Facilities[Facility], Outages[Facility3] )
)

Total Duration = [Duration 1] + [Duration 2] + [Duration 3]

My sample file is attached for you to look at.

You could also do all the calcs in a single measure if you prefer.

Total Duration 2 = 
VAR _Loc1 = 
    CALCULATE (
        SUM ( Outages[Duration1] ),
        USERELATIONSHIP ( Facilities[Facility], Outages[Facility1] )
    )
VAR _Loc2 = 
    CALCULATE (
        SUM ( Outages[Duration2] ),
        USERELATIONSHIP ( Facilities[Facility], Outages[Facility2] )
    )
VAR _Loc3 = 
    CALCULATE (
        SUM ( Outages[Duration3] ),
        USERELATIONSHIP ( Facilities[Facility], Outages[Facility3] )
    )
RETURN _Loc1 + _Loc2 + _Loc3

[kkanda]

Thank you, jdbuchanan, this solution worked.