Forum Discussion

babajat's avatar
babajat
Frequent Visitor
4 years ago
Solved

Find if multiple values are occuring before other columns

Hello again!

 

I have this dataset with a series of interventions and a date for each intervention:

INDXINT 1DATE INT 1INT 2DATE INT 2INT 3DATE INT 3INT 4DATE INT 4INT 5DATE INT 5INT 6DATE INT 6
1R06/01/2022T08/04/2022U04/08/2022O01/09/2022P06/04/2022XX05/01/2022
2Q17/01/2022T06/01/2022B01/01/2022      
3LL05/06/2022XX02/01/2022JJ04/01/2022YY02/01/2022    
4XX04/01/2022UU04/01/2022Q05/01/2022TT10/01/2022    
5HH04/01/2022FF09/04/2022AA17/01/2022VV05/09/2022    
6PP09/01/2022LL06/01/2022YY03/01/2022HH04/05/2022XX04/01/2022XX07/01/2022
7FF03/01/2022OO04/01/2022XX05/01/2022      
8YY02/01/2022XX01/01/2022AA03/01/2022      
9YY05/01/2022JJ01/01/2022YY06/01/2022LL04/09/2022    

 

I want to get 1 if XX OR YY occur before any other intervention. So, for example, on the first row i will return a 1 and in the 7 row it won't return nothing.

If XX and YY occur together before anything else (like in row 3) it's ok to get a 1; if XX or YY occur on the first day but togeter with something (like in row 4) else i would like to get nothing.

 

Thanks in advance for the help!

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    That source data is in rather bad shape.  First step is to unpivot it into something usable

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLCoMwEEV/pWQt5GESzdIuRERQSxVF/P/faKbGIQ8tXQTiwZzJ3Mm2EU4y8rKLaco4FUwI+/EGUFImTzABkNQyB3oAnDJzgsE58MiyAFFo3bONAB/t4kVczK/+dG4Ej8sFwtzuus4V0mFp4Rna1jWAZF3jf1K7RJd/cppiMoad2pagJ85+ypXdNU2sqmsgxsuxquLA5tnVM/d2DSMZnAtPHlHpJIbcI3gnFcbp3/IgRTDbAm/v2/r++qz6a7zl5aAOg/9AvhkFde+dBp0qeSA8SUYn6cn73PcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INDX = _t, #"INT 1" = _t, #"DATE INT 1" = _t, #"INT 2" = _t, #"DATE INT 2" = _t, #"INT 3" = _t, #"DATE INT 3" = _t, #"INT 4" = _t, #"DATE INT 4" = _t, #"INT 5" = _t, #"DATE INT 5" = _t, #"INT 6" = _t, #"DATE INT 6" = _t]),
    CT = (List.Count(Table.ColumnNames(Source))-1)/2,
    #"Merged Columns1" = List.Accumulate({1..CT},Source,(state,current)=>Table.CombineColumns(state,{"INT " & Text.From(current), "DATE INT " & Text.From(current)},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged " & Text.From(current))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"INDX"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " , ")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"INT", "Date"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"INDX", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"INDX", "INT", "Date"})
in
    #"Removed Other Columns"

    After that you can create a measure that implements your desired logic

    XX or YY is first Int = 
var a = min('Table'[Date])
var i = CALCULATE(min('Table'[INT]),'Table'[Date]=a)
return if (i in {"XX","YY"},1,0)

     

    see attached.

     

    Thank you for the challenge - the unpivoting part was tricky as it needed pairs of columns.

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    That source data is in rather bad shape.  First step is to unpivot it into something usable

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLCoMwEEV/pWQt5GESzdIuRERQSxVF/P/faKbGIQ8tXQTiwZzJ3Mm2EU4y8rKLaco4FUwI+/EGUFImTzABkNQyB3oAnDJzgsE58MiyAFFo3bONAB/t4kVczK/+dG4Ej8sFwtzuus4V0mFp4Rna1jWAZF3jf1K7RJd/cppiMoad2pagJ85+ypXdNU2sqmsgxsuxquLA5tnVM/d2DSMZnAtPHlHpJIbcI3gnFcbp3/IgRTDbAm/v2/r++qz6a7zl5aAOg/9AvhkFde+dBp0qeSA8SUYn6cn73PcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INDX = _t, #"INT 1" = _t, #"DATE INT 1" = _t, #"INT 2" = _t, #"DATE INT 2" = _t, #"INT 3" = _t, #"DATE INT 3" = _t, #"INT 4" = _t, #"DATE INT 4" = _t, #"INT 5" = _t, #"DATE INT 5" = _t, #"INT 6" = _t, #"DATE INT 6" = _t]),
    CT = (List.Count(Table.ColumnNames(Source))-1)/2,
    #"Merged Columns1" = List.Accumulate({1..CT},Source,(state,current)=>Table.CombineColumns(state,{"INT " & Text.From(current), "DATE INT " & Text.From(current)},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged " & Text.From(current))),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"INDX"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> " , ")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Value", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"INT", "Date"}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Split Column by Delimiter", {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"INDX", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"INDX", "INT", "Date"})
in
    #"Removed Other Columns"

    After that you can create a measure that implements your desired logic

    XX or YY is first Int = 
var a = min('Table'[Date])
var i = CALCULATE(min('Table'[INT]),'Table'[Date]=a)
return if (i in {"XX","YY"},1,0)

     

    see attached.

     

    Thank you for the challenge - the unpivoting part was tricky as it needed pairs of columns.