Count Multiple Columns

[vincentakatoh]

Hi,

Need help to count the part usage. Below is sample data,

Case_Number	PartNumber_Part replace_1st	PartNumber_Part replace_2nd	PartNumber_Part replace_3rd	PartNumber_Part replace_4th	PartNumber_Part replace_5th
1	1000123	null	null	null	null
2	1000124	1000123	1000125	1000126	1000127
3	1000128	1000123	null	null	null
4	1000124	1000125	null	null	null
5	1000126

Required result

Part Number	Usage
1000123	3
1000124	2
1000125	2
1000126	2
1000127	1
1000128	1

Thanks a bunch. Tried searching for similar ques, but no luck.

[AlB]

Hi @vincentakatoh 

You should unpivot the partNumber columns in the query editor 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAwNDIGsvJKc3KwU7E60UpGcLUmKLogLFM4ywzOMgfrQ6ixIGQbSLkJFmtMcStHtVcBBcfGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Case_Number = _t, #"PartNumber_Part replace_1st" = _t, #"PartNumber_Part replace_2nd" = _t, #"PartNumber_Part replace_3rd" = _t, #"PartNumber_Part replace_4th" = _t, #"PartNumber_Part replace_5th" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case_Number", Int64.Type}, {"PartNumber_Part replace_1st", Int64.Type}, {"PartNumber_Part replace_2nd", Int64.Type}, {"PartNumber_Part replace_3rd", Int64.Type}, {"PartNumber_Part replace_4th", Int64.Type}, {"PartNumber_Part replace_5th", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Case_Number"}, "Attribute", "Value")
in
    #"Unpivoted Columns"

to get something like this. Fromt that you ance get what you want easily:

Case_NumberAttributeValue

1	PartNumber_Part replace_1st	1000123
2	PartNumber_Part replace_1st	1000124
2	PartNumber_Part replace_2nd	1000123
2	PartNumber_Part replace_3rd	1000125
2	PartNumber_Part replace_4th	1000126
2	PartNumber_Part replace_5th	1000127
3	PartNumber_Part replace_1st	1000128
3	PartNumber_Part replace_2nd	1000123
4	PartNumber_Part replace_1st	1000124
4	PartNumber_Part replace_2nd	1000125
5	PartNumber_Part replace_1st	1000126

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[vincentakatoh]

@AlB 

Thanks for the response, the original data has several more columns. Unpivoting will mess up other calculations which I'm trying to avoid. Please advise if theres alternate method.

Original data also has 100s of different part numbers.

Thanks.

[v-lid-msft]

Hi @vincentakatoh ,

We can use the following steps to meet your requirement:

1. create a custom column in fact table:

"," & Text.Combine(List.Transform(Record.ToList(Record.SelectFields(_,List.Select(Table.ColumnNames(NameOfYourLastStep), each Text.Contains(_, "PartNumber")))), Text.From), ",") & ","

2. Create another query table:

let
    Source = Table.SelectColumns(FactTable,List.Select(Table.ColumnNames(FactTable), each Text.Contains(_, "PartNumber"))),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Columns",{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

3. create a measure to calculate the result:

Count = SUMX(DISTINCT('Query1'[Value]),var v=[Value] return SUMX('FactTable',round(DIVIDE(LEN([Custom])-LEN(SUBSTITUTE([Custom],","&v&",","")),LEN(","&v&","),0),0)))

we use the Text.Contains(_, "PartNumber") to determine the partnumber column, if mean the condition of column name is it contain "Part Number", you can use Text.StartsWith(_, "PartNumber_Part replace_") , it will be more Strict.

By the way, PBIX file as attached.

Best regards,

[az38]

Hi @vincentakatoh 

maybe it can look like a measure

Measure = 
var PartNumber = MAX(Table[PartNumber_Part replace_1st])

RETURN

CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), Table[PartNumber_Part replace_1st] = PartNumber)) +
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), Table[PartNumber_Part replace_2st] = PartNumber)) + 
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), Table[PartNumber_Part replace_3st] = PartNumber)) + 
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), Table[PartNumber_Part replace_4st] = PartNumber)) + 
CALCULATE(COUNTROWS(Table), FILTER(ALL(Table), Table[PartNumber_Part replace_5st] = PartNumber))  

then leave in the visual [PartNumber_Part replace_1st] field and new measure

[vincentakatoh]

@az38 

Thanks. Nonetheless, the original data has 100s of different part number. Writing each "part number" in the DAX will be difficult or impossible.

Rgds, Vincent

[az38]

@vincentakatoh 

so, I think @AlB solution would be the most appropriative to this case