Forum Discussion

vincentakatoh's avatar
vincentakatoh
Helper IV
6 years ago

Count Multiple Columns

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 P...
Need Help
vincentakatoh's avatar
vincentakatoh
Helper IV
6 years ago

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's avatar
v-lid-msft
Community Support
6 years ago

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,