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_NumberPartNumber_Part replace_1stPartNumber_Part replace_2ndPartNumber_Part replace_3rdPartNumber_Part replace_4thPartNumber_Part replace_5th
11000123nullnullnullnull
210001241000123100012510001261000127
310001281000123nullnullnull
410001241000125nullnullnull
51000126    

 

Required result

 

Part NumberUsage
10001233
10001242
10001252
10001262
10001271
10001281

 

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

Need Help

6 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

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

      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

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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'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,