Forum Discussion

PawelPowerBI's avatar
PawelPowerBI
New Member
3 years ago
Solved

Count IF with mutliple ifs date issue

hi I am dealing with a report that is reviewed & updated by multiple users.

 

The report is in a database. Whenever a row is updated an edit date is added.

I am looking to create a dashboard that presents timeline and the number of edits for a given day.

 

 

My idea so far was to extract the date of each seperate update into a column. And i am able to do seperate pivots for each edit 

but I am unable to get it to what i want to see. 

 

Edit1 = IF(Data[Edit #*]=1,Data[Edit Date*],"ignore")
Edit2a = if(Data[Edit #*]=2,left(Data[Edit Date*],11),"ignore")
Edit2b = if(Data[Edit #*]=2,RIGHT(Data[Edit Date*],11),"ignore")
EDIT3a = if(Data[Edit #*]=3,left(Data[Edit Date*],11),"ignore")
EDIT3b = if(Data[Edit #*]=3,right(left(Data[Edit Date*],25),11),"ignore")
EDIT3c = if(Data[Edit #*]=3,RIGHT(Data[Edit Date*],11),"ignore")

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi PawelPowerBI,

    I'd like to suggest you expand these date value strings and add an index as 'edit' step, then you can simply use these value to create graphs and analysis.

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUYo2MjAy1jXRNTSJVYCzzWKVYnWilQyA8mCGIZJCI6ikMVzMUNfQAKbZSNcQyRygylgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Edit = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Edit", Int64.Type}, {"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddIndexColumn(Table.FromList(List.Select(Text.Split([Date]," "),each _ <> "")),"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Index"}, {"SubDate", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom","[","",Replacer.ReplaceText,{"SubDate"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"SubDate"})
in
    #"Replaced Value1"

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi PawelPowerBI,

    I'd like to suggest you expand these date value strings and add an index as 'edit' step, then you can simply use these value to create graphs and analysis.

    Full query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLSUYo2MjAy1jXRNTSJVYCzzWKVYnWilQyA8mCGIZJCI6ikMVzMUNfQAKbZSNcQyRygylgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Edit = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Edit", Int64.Type}, {"Date", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddIndexColumn(Table.FromList(List.Select(Text.Split([Date]," "),each _ <> "")),"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Index"}, {"SubDate", "Index"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom","[","",Replacer.ReplaceText,{"SubDate"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","]","",Replacer.ReplaceText,{"SubDate"})
in
    #"Replaced Value1"

    Regards,

    Xiaoxin Sheng