Forum Discussion

Prabunathan's avatar
Prabunathan
Frequent Visitor
8 years ago
Solved

Dynamic Filtering

Hi,   I have a situaiton, where in i have to extract the trasactions which happened due to a specific promotion ( example a Gift Voucher Promotion ). From the millions of transactions, i have to ge...
Need Help
  • stretcharm's avatar
    stretcharm
    8 years ago

    Apologies I didn't read the examples.

    Is the Item code a load filter or a Slicer on all the data?

     

     If a slicer then add a measure like this and Filter by the slicer for Item code and  in the filter pane HasGV>0

    HasGV = 
VAR GV = "GV100"
RETURN
    CALCULATE (
        COUNT ( Trans[Transaction Id] ),
        FILTER (
            ALL ( Trans ),
            Trans[Transaction Id] = MAX ( Trans[Transaction Id] )
                && Trans[Item Code] = GV
        )
    )
stretcharm's avatar
stretcharm
Icon for Memorable Member rankMemorable Member
8 years ago

 

Is the data in Columns? If so can you just filter the Description that Starts with Gift voucher

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZJda8IwFIb/ykt3szEdra37uOywc0ILndYPEC9CG2dAEzCRsX+/NLG6QutgjEHLgffkOec9J1kuHdfrDmje9R6cjhO6rqdD+mpjKA8S6UZwioVVfGfVaUSGM891y8jWmIlDvqF7WOWIBDWkd7HLvUX8GuJfRII2pN1Y0GQs0GGe2JpzIjeMvyMhuY4Uz1Z+tFyvxvV/5p7auHaLFkG0CJM0joCyDGp/mX8ZxVk0bkxdQaxhtoYpZ0piIrYFDrzQLYYzpHuxE4oJjmuwO4pMKLItkTf1iQ/tg4JA7QmXJDfHRgPkgivCuDyWJbyAGQDbclam6E7ipnRw/swQjf7G0WQaZ0cpqzXSgnZxOllxYfUQGxK+udVTolpb71dbM7f5l/uyBW//e1t9846qRGOnb8rqCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, #"Transaction Id" = _t, #"Item Code" = _t, #"Item Description" = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Transaction Id", type text}, {"Item Code", type text}, {"Item Description", type text}, {"Qty", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Item Description], "Gif Voucher") or Text.StartsWith([Item Description], "Gift Voucher"))
in
    #"Filtered Rows"
  • Prabunathan's avatar
    Prabunathan
    Frequent Visitor
    8 years ago

    Hi Sir,

     

    Thank you for your guidance. May be i was not specific in my question on the earlier post.

     

    I need to actually filter the table by a search criteria by Items, and the rsulting table should be with transaction ID's which have the specific  "item"  + " Gift Vouchers " in them. 

     

    Just getting the total of Gift vouchers issued is not the requirement.

     

    Thanks in advance.

     

    Prabunathan

    • stretcharm's avatar
      stretcharm
      Icon for Memorable Member rankMemorable Member
      8 years ago

      Apologies I didn't read the examples.

      Is the Item code a load filter or a Slicer on all the data?

       

       If a slicer then add a measure like this and Filter by the slicer for Item code and  in the filter pane HasGV>0

      HasGV = 
VAR GV = "GV100"
RETURN
    CALCULATE (
        COUNT ( Trans[Transaction Id] ),
        FILTER (
            ALL ( Trans ),
            Trans[Transaction Id] = MAX ( Trans[Transaction Id] )
                && Trans[Item Code] = GV
        )
    )
      • Prabunathan's avatar
        Prabunathan
        Frequent Visitor
        8 years ago

        Hi

         

        Thank you and it works fantastic.