Forum Discussion

arlequin71's avatar
arlequin71
Helper II
7 years ago
Solved

Filter a visual based on ranked dimension

Hello, could you please advice me on how to proceed to filter a chart based on Customer Ranking?. As shown in the image i need the customer filter above each visual changes (and filter the visual) according to its ranking (Ranking #1, #2,#3 and #4). I really appreciate your help on this. Thanks in advance.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZVdJji03DLtKo9deWIMHrXOMj15mGQTIcP9Qz5Rfp3pnFDxIFEmpfv36/O3fv//584/f//qQz/b5oWM1l/WRa1neluhrbdFinqXjs01/rdVWmyGv9djRunLddxPZZ79ai9ojvQ2Lsw5rvue53qTtzf2rYz/u/2rfotPXNsVx7+fpiWvtXGtrNVvnKt27zXHCQ6TTT9QyRlvLzpYlba5xjoo217Pde7TBqDV6U19Mvrcexsza6OsRnL1O79V2P7m5aws9gdqcrfcTnAReW3xhA0c5e/CA9fN5SxtMZQIUZ8gx29xVAGtGENV368JiiDTT+YjNX+8Cd2c2o3dgrVwP1IMgjmg+zrWmWePFXNryyZeRYgHaV4vBkuHl4NE929CzfThw7uvib2M8ghsHuNH24K1Lm5IsDnJFP2UaCE43iQMyKss0AhEtlgmR1lkJRNSNCfjFUXSg3Ly/33wFNdrxhG6e6PRSVm00ZXIioC9l4FNRyvMCEih1uClUMAgRikaexcZuJ7WkdT9bFKKxTnYPaUKiGG7v0x7BrddNeGB1gr0RG7OX3baRFYhfBrXhGX9cFl2yY+0M1ECJcgDHPWEF0WjdSExtU4rReU08gtsHa3B2sa4T0c1O5qTkznecXkqKd4dOnGXt13pSJ2K1BoqzLGkC3XlVXD6kEyqeJxnxwP79iC5OnoEnyJzhLVi/kWzZdRwZ9HoCdOnv54Jm4hGgDlkao0mFl+RXVhb0UtbJ12zmhQRCHU83kX5qCEpuxgcINtODBLUEP7StWcjA32h7QxCGjbtei+4L8s/oV2hVhAHEhKlBEVe/mqKLJ/FEDnxwESt/BIkJgem6nB9wkalFz4EOQgjg9hJlWsiBzqTYv6xg7YCSHFPQmevMx3kWTi/+tBTRcy3C0FKtXtI4Kj1YifTOHXGNUCuFlW7M55DaKt7Cml3KOHdb5TWAdZe54q1dhoo0Z+gzPjvVhdn2iglsKiizLFczEAThMINFEiX0Gb19Bk8HQ0L/HUUSA/DlU+CwFQ9x46ATdmg+ifH/6PwgA66zRSTXF1FKws1JxLK5smWjUUIncdEr3o60EhpdcrVoj9a9nR6ZFslG2VP07CjDQJGnrcjpF1CWV3Ma2Xl4Pvufv9dz82k4V9EbToh6EtNs8WxP6QZOM0k33xVqGhTlA+9Zs66EONfTV2QeM0UaZcp4OoLXpko2Ww+QmaUGz+bB2lp64H63Kpo4WNUL045r9KzngnvWZzROEs9TR/rDVlZpum5VSX0TsHzCKzoIo+5FdJs2lkDuVW+ACtyTIDl1LpYCowBS8ySwOPpE9UXo2fYPYezjGd8mPLkzT7rpJqNfpK+OBmEsKXtLrTIkyymQ38Pv/hzxjIJ58YijFkaKTWlDgdF/6CIOz+HdBC8v5eCRbbW6VY5Cvvs75/Lq6TfQF1wVWwumiOqhSfBzzoxci73HQPQO3c/YtJdbGCcJ9ClQMC7YvSYquOLe1f/R/8pUsb9H9fkcY0kKJBlUSCagRmpipvBdg002Et6D+cj9qVo9fxaYJSzK3AFeNS5UymvKtbQ2viEgEtu+xLpjAn4ZkCcJBis0zqaK/rxHAZ+KJxPw7uKdOTL0H/1WXx0DnqRWpgKv4thj6I2TqSYc88Iht5eOrC33vPTC0WDAxO6cZHrs9gU3UisTyoG3dAeEfshW7SATbz+znDLqOBghVd1xKw2WfPsZiVs5x/+LG1ESuz73PQXDFLNYEdOUMDWI1r7sB/u8fFiqEfV3I3LcZVF/ZpDDKu0BmsmYkM+Qqm5chhqqLhxpPM23l21hIPT6AZ3Xn9Evzk/q19d/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"1/1/2018" = _t, #"2/1/2018" = _t, #"3/1/2018" = _t, #"4/1/2018" = _t, #"5/1/2018" = _t, #"6/1/2018" = _t, #"7/1/2018" = _t, #"8/1/2018" = _t, #"9/1/2018" = _t, #"10/1/2018" = _t, #"11/1/2018" = _t, #"12/1/2018" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"1/1/2018", Int64.Type}, {"2/1/2018", Int64.Type}, {"3/1/2018", Int64.Type}, {"4/1/2018", Int64.Type}, {"5/1/2018", Int64.Type}, {"6/1/2018", Int64.Type}, {"7/1/2018", Int64.Type}, {"8/1/2018", Int64.Type}, {"9/1/2018", Int64.Type}, {"10/1/2018", Int64.Type}, {"11/1/2018", Int64.Type}, {"12/1/2018", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer Name"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Date"}, {"Value", "Sales"}}),
    #"Parsed Date" = Table.TransformColumns(#"Renamed Columns",{{"Date", each Date.From(DateTimeZone.From(_)), type date}})
in
    #"Parsed Date"

 

 

  • Hi arlequin71 ,

     

    Assuming that your ranking measure looks something like this:

    ranking = RANKX(ALL(Query1[Customer Name]);CALCULATE(SUM(Query1[Sales])))

    Just add the customer name to the legend (you can then hide the legend), and then add your ranking measure to the visual filter, for each one of the top for select ranking equal 1, 2, 3 and 4 and you should have the required result.

     

    Regards,

    MFelix

2 Replies

  • Hi arlequin71 ,

     

    Assuming that your ranking measure looks something like this:

    ranking = RANKX(ALL(Query1[Customer Name]);CALCULATE(SUM(Query1[Sales])))

    Just add the customer name to the legend (you can then hide the legend), and then add your ranking measure to the visual filter, for each one of the top for select ranking equal 1, 2, 3 and 4 and you should have the required result.

     

    Regards,

    MFelix

    • arlequin71's avatar
      arlequin71
      Helper II

      It works, thanks MFelix 

      Just wondering now how to implement the same solution in a Line and Clustered Column Chart.

       

      Regards,

       

      Alex-