Forum Discussion

Shruthi96's avatar
Shruthi96
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 i...
Need Help
  • ThxAlot's avatar
    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's avatar
    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
Ahmedx's avatar
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's avatar
    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 1AAA,,,
    • Ahmedx's avatar
      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

       

      • Shruthi96's avatar
        Shruthi96
        Helper III
        3 years ago

        Thank you for replying, I tried both the options. However, it gives repeated values.  Like, AAA,AAA,AAA,AAA

    • Ahmedx's avatar
      Ahmedx
      Super User
      3 years ago

      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)