Forum Discussion

Gauthier_cnl's avatar
Gauthier_cnl
Regular Visitor
4 years ago
Solved

Hierarchy parent child problem

Hi,

 

I'm working with parent-child hierarchy and I want do to do something but I'm not sure if it's possible.

Here an example of what I'm trying to do :

 

There are 4 columns : ID, parent ID, Process and the path determinated by the DAX function.

I've added a new column which evaluate if Process= "Freezing

 

 

Now I want to add a new column, if Isfreezing= true then "A" but I want that the other processes that belong to the hierarchy also to get "A". So I don't know which DAX function I have to use to finally have this result :

 

 

Thanks !

Need Help
  • CNENFRNL's avatar
    CNENFRNL
    4 years ago
    let
    REC = (l as list, id) as list =>
        if id is null then l else
            let
                p = List.PositionOf(Sup, id)            
            in if p<>-1 then REC(l & {id}, Init{p}) else l & {id},

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKiCqVYnWglIyALJFAJ5hlDeVVgngmQBZIvB/NMgSyQiFtRampVZl46WNAMKpgG5pkDWSAjisE8QwOIRfkQi0A8sBCKAcYgEbBcIcROOD8ZYiuICRYsU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ID_INIT = _t, PROCESS = _t]),
    Sup = Source[ID],
    Init = Source[ID_INIT],
    #"Added Path" = Table.AddColumn(Source, "Path", each {[ID]} & REC({}, [ID_INIT])),
    #"ID Freezing" = let id = #"Added Path"[ID] in List.Transform(List.PositionOf(#"Added Path"[PROCESS], "Freezing", 2), each id{_}),
    #"All Freezing" = List.Distinct(List.Combine(List.Select(#"Added Path"[Path], each List.Count(List.Intersect({_, #"ID Freezing"}))>0))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Path", each [Path], each if List.Contains(#"All Freezing", [ID]) then "A" else "", Replacer.ReplaceValue, {"Path"})
in
    #"Replaced Value"

1 Reply

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    4 years ago
    let
    REC = (l as list, id) as list =>
        if id is null then l else
            let
                p = List.PositionOf(Sup, id)            
            in if p<>-1 then REC(l & {id}, Init{p}) else l & {id},

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQKiCqVYnWglIyALJFAJ5hlDeVVgngmQBZIvB/NMgSyQiFtRampVZl46WNAMKpgG5pkDWSAjisE8QwOIRfkQi0A8sBCKAcYgEbBcIcROOD8ZYiuICRYsU4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, ID_INIT = _t, PROCESS = _t]),
    Sup = Source[ID],
    Init = Source[ID_INIT],
    #"Added Path" = Table.AddColumn(Source, "Path", each {[ID]} & REC({}, [ID_INIT])),
    #"ID Freezing" = let id = #"Added Path"[ID] in List.Transform(List.PositionOf(#"Added Path"[PROCESS], "Freezing", 2), each id{_}),
    #"All Freezing" = List.Distinct(List.Combine(List.Select(#"Added Path"[Path], each List.Count(List.Intersect({_, #"ID Freezing"}))>0))),
    #"Replaced Value" = Table.ReplaceValue(#"Added Path", each [Path], each if List.Contains(#"All Freezing", [ID]) then "A" else "", Replacer.ReplaceValue, {"Path"})
in
    #"Replaced Value"