Hi Folks, I am currently seeking a solution for my project within Power BI, utilizing DAX code. Within this endeavor, I possess a table containing the information outlined below. The task at hand involves segmenting this data by comma, extracting the highest four counts, and subsequently presenting the corresponding subcategories in a comma-separated format. Category Sub CategoryCategory 1AAACategory 2BBB,CCC,AAACategory 3DDD,EEE,ZZZCategory 4ZZZCategory 1AAACategory 1BBBCategory 2YYYCategory 3EEECategory 4GGGCategory 2ZZZ,DDDCategory 1SSSCategory 2OOOCategory 3PPP,RRRCategory 4JJJ,KKKCategory 3LLL,WWWCategory 3DDD,EEE,ZZZCategory 3DDD,EEE,ZZZCategory 3DDD,EEE,ZZZBig list continues Required output in below form below, Top 4Category 1AAA,BBB,SSSCategory 2BBB,CCC,AAA,OOOCategory 3DDD,EEE,ZZZCategory 4GGG,JJJ,KKK,ZZZ Thanks

Helper III

3 years ago

Solved

Unique text split with top 4 categories

Hi Folks,

I am currently seeking a solution for my project within Power BI, utilizing DAX code. Within this endeavor, I possess a table containing the information outlined below. The task at hand involves segmenting this data by comma, extracting the highest four counts, and subsequently presenting the corresponding subcategories in a comma-separated format.

Category	Sub Category
Category 1	AAA
Category 2	BBB,CCC,AAA
Category 3	DDD,EEE,ZZZ
Category 4	ZZZ
Category 1	AAA
Category 1	BBB
Category 2	YYY
Category 3	EEE
Category 4	GGG
Category 2	ZZZ,DDD
Category 1	SSS
Category 2	OOO
Category 3	PPP,RRR
Category 4	JJJ,KKK
Category 3	LLL,WWW
Category 3	DDD,EEE,ZZZ
Category 3	DDD,EEE,ZZZ
Category 3	DDD,EEE,ZZZ
Big list continues

Required output in below form below,

	Top 4
Category 1	AAA,BBB,SSS
Category 2	BBB,CCC,AAA,OOO
Category 3	DDD,EEE,ZZZ
Category 4	GGG,JJJ,KKK,ZZZ

Thanks

Need Help

ThxAlot

3 years ago

I don't seem anything to do with DAX but only some tricky transformation by PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t]),

    Grouped = Table.Group(
        Source,
        "Category",
        {"TOP4", each let
            sorted = Table.Sort(_,{{"Sub Category", Order.Ascending}}),
            combined = Text.Combine(List.Distinct(List.FirstN(sorted[Sub Category],4)),","),
            truncated = Text.BeforeDelimiter(combined, ",", 3)
            in truncated
        })
in
    Grouped

Ahmedx

3 years ago

Measure 4 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = {PATHITEM(_text,1),PATHITEM(_text,2),PATHITEM(_text,3),PATHITEM(_text,4)}
RETURN 
CONCATENATEX(FILTER(t1,[Value]<> BLANK()),[Value],", ",[Value],ASC)

Measure 2 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = if(NOT ISBLANK(PATHITEM(_text,1)),PATHITEM(_text,1)&",")
VAR t2 = if(NOT ISBLANK(PATHITEM(_text,2)),PATHITEM(_text,2)&",")
VAR t3= if(NOT ISBLANK(PATHITEM(_text,3)),PATHITEM(_text,3)&",")
VAR t4 = if(NOT ISBLANK(PATHITEM(_text,4)),PATHITEM(_text,4))
RETURN 
t1&t2&t3&t4

13 Replies

ThxAlot

Super User

3 years ago

I don't seem anything to do with DAX but only some tricky transformation by PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t]),

    Grouped = Table.Group(
        Source,
        "Category",
        {"TOP4", each let
            sorted = Table.Sort(_,{{"Sub Category", Order.Ascending}}),
            combined = Text.Combine(List.Distinct(List.FirstN(sorted[Sub Category],4)),","),
            truncated = Text.BeforeDelimiter(combined, ",", 3)
            in truncated
        })
in
    Grouped

Ahmedx

Super User

3 years ago

pls try rhis

Measure = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
RETURN 
PATHITEM(_text,1) &","&PATHITEM(_text,2) &","&  PATHITEM(_text,3) &","&PATHITEM(_text,4)

Shruthi96

Helper III

3 years ago

Thank you.

Is there is a way to remove "," incase if there is no 3rd or 4th text.

Example:

Category 1

AAA,,,

Ahmedx

Super User

3 years ago

pls try this

Measure 2 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = if(NOT ISBLANK(PATHITEM(_text,1)),PATHITEM(_text,1)&",")
VAR t2 = if(NOT ISBLANK(PATHITEM(_text,2)),PATHITEM(_text,2)&",")
VAR t3= if(NOT ISBLANK(PATHITEM(_text,3)),PATHITEM(_text,3)&",")
VAR t4 = if(NOT ISBLANK(PATHITEM(_text,4)),PATHITEM(_text,4))
RETURN 
t1&t2&t3&t4

ToddChitt
Super User
3 years ago
Why does Category 2 not include YYY? It is the 7th entry in your table. Should the result not be:
Category 2 | BBB,CCC,AAA,OOO,YYY
?
I think in Power Query (not DAX) you have the ability to split into Rows
That breaks it down to pairs records. You then need to combine them back. Not sure how to do that yet, but working on it.

Manoj_Nair

Solution Supplier

3 years ago

Shruthi96- Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up.

let
    // Load the source data
    Source = Table.FromRecords({
        [Category="Category 1", SubCategory="AAA"],
        [Category="Category 2", SubCategory="BBB,CCC,AAA"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 4", SubCategory="ZZZ"],
        [Category="Category 1", SubCategory="AAA"],
        [Category="Category 1", SubCategory="BBB"],
        [Category="Category 2", SubCategory="YYY"],
        [Category="Category 3", SubCategory="EEE"],
        [Category="Category 4", SubCategory="GGG"],
        [Category="Category 2", SubCategory="ZZZ,DDD"],
        [Category="Category 1", SubCategory="SSS"],
        [Category="Category 2", SubCategory="OOO"],
        [Category="Category 3", SubCategory="PPP,RRR"],
        [Category="Category 4", SubCategory="JJJ,KKK"],
        [Category="Category 3", SubCategory="LLL,WWW"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"]
    }),

    // Expand the SubCategory values
    SplitSubCat = Table.ExpandListColumn(Table.TransformColumns(Source, {{"SubCategory", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), type list}}), "SubCategory"),

    // Group by Category and aggregate SubCategory values
    GroupedRows = Table.Group(SplitSubCat, {"Category"}, {{"AllData", each _, type table [Category=text, SubCategory=text]}}),

    // Extract unique SubCategory values and transform back into comma-separated format
    TransformToList = Table.AddColumn(GroupedRows, "SubCategory", each Text.Combine(List.Distinct(Table.Column([AllData], "SubCategory")), ",")),

    // Remove unnecessary columns
    FinalTable = Table.SelectColumns(TransformToList, {"Category", "SubCategory"})
in
    FinalTable

Mkarwa-123
Resolver II
3 years ago
Hi Shruthi96
Use power query for this.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Category " = _t, #"Sub Category" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category ", type text}, {"Sub Category", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Sub Category", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Sub Category.1", "Sub Category.2", "Sub Category.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sub Category.1", type text}, {"Sub Category.2", type text}, {"Sub Category.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Category "}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates1", {"Category "}, {{"All", each Text.Combine(List.Distinct([Value]),", ")}})
in
#"Grouped Rows"

Forum Discussion

Unique text split with top 4 categories

13 Replies

Related content

Splitting Column values

How to map data to categories and sub categories (appr. 500 unique rows)

Unique Count in Row convert

Average of categories over vendor

monthly split per category