Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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
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)
Thank you.
Is there is a way to remove "," incase if there is no 3rd or 4th text.
Example:
Category 1 | AAA,,, |
and try this
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)
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
Thank you for replying, I tried both the options. However, it gives repeated values. Like, AAA,AAA,AAA,AAA
I fixed my mistake, please try again
code please
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
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"
@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
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
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
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.
Proud to be a Super User! | |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |