Forum Discussion

acaryasin's avatar
acaryasin
Frequent Visitor
4 years ago
Solved

How to create a measure to filter data (desired column example below)

Hello everyone,   I have two tables below. I explained what I need in desired column. Is there anybody to help me a creating kind of measure?   Thanks in advance,       ID Task Statu T...
General Comment
  • lbendlin's avatar
    lbendlin
    4 years ago

    Why would this need to be a measure? Can the result be impacted by user interaction?

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9D4IwEIb/SsPsAn7AKvi5OOjgQBiIXkiTUght8O97FA0t0EaHNznI0+vdA2nq+Wtv4SVVWTOQ8MR6i/ExV5ANhRawJF62+JD3nErKC6xiTNDxQoCQJXBpkpdKEiHzpu+aYJaqa0vhRXyTPXNSN1XRgBD4tMOs1Fu8K2dEcHD23mP0Lcbdk5w/gDHFHjCbYY7Att0RE3bbxaRHx/N0Z4JwMovVnmLNPR0GFa3PbfXXk9r3c9pT9LClw9zMblZ3kxnm7Glbzp45YaLuFspx6pthI/rDc/TrXzoi7YZNzu3XZF1+TfLrVjeGYPYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Task Statu" = _t, #"Task Name" = _t, Order = _t, Description = _t, #"Desired Output / Measure" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Output",(k) => if k[Description] = "AB Review" then try (Table.SelectRows(Source,each [Task Statu]="In progress" and [ID]=k[ID])){0}[Description] otherwise null
else null
)
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

     

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

Why would this need to be a measure? Can the result be impacted by user interaction?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZI9D4IwEIb/SsPsAn7AKvi5OOjgQBiIXkiTUght8O97FA0t0EaHNznI0+vdA2nq+Wtv4SVVWTOQ8MR6i/ExV5ANhRawJF62+JD3nErKC6xiTNDxQoCQJXBpkpdKEiHzpu+aYJaqa0vhRXyTPXNSN1XRgBD4tMOs1Fu8K2dEcHD23mP0Lcbdk5w/gDHFHjCbYY7Att0RE3bbxaRHx/N0Z4JwMovVnmLNPR0GFa3PbfXXk9r3c9pT9LClw9zMblZ3kxnm7Glbzp45YaLuFspx6pthI/rDc/TrXzoi7YZNzu3XZF1+TfLrVjeGYPYG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Task Statu" = _t, #"Task Name" = _t, Order = _t, Description = _t, #"Desired Output / Measure" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Output",(k) => if k[Description] = "AB Review" then try (Table.SelectRows(Source,each [Task Statu]="In progress" and [ID]=k[ID])){0}[Description] otherwise null
else null
)
in
    #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".