Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Rankx

    Silver Contributor
    Posted Oct 21, 2022 01:13 PM
      |   view attached
    Hi

    I want to create rank based on another column. attached example raw data, i have org name , stockroom and product total three columns.

    so here is need rank for my product based on stockroom and org . please help me on this.

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------

    Attachment(s)

    xlsx
    rank data.xlsx   14 KB 1 version


  • 2.  RE: Rankx

    Silver Contributor
    Posted Oct 25, 2022 02:10 PM
    Hi All,

    here with mentioned the output what am expecting,

    Org Name Stockroom name Product Rank
    ACCD AA1 112-2371 1
    ACCD AA2 112-2371 1
    ACCD AA2 112-2371 2
    ACCD AA4 489-0107 1
    ACCD AA5 489-0107 1
    BPL BA1 112-2371 1
    BPL BA1 489-0107 1
    BPL BA1 IBSAWV120ST-200 1
    BPL BA1 IBSAWV120ST-200 2
    BPL BA1 IBSAWV120ST-200 3
    BPL BA4 112-2371 1
    BPL BA7 489-0107 1
    Zebronic ZB1 113-1131 1
    Zebronic ZB1 113-1133 1
    Zebronic ZB1 113-1134 1
    Zebronic ZB1 113-1134 2
    Zebronic ZB2 113-1131 1
    Zebronic ZB2 113-1131 2


    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------



  • 3.  RE: Rankx

    Bronze Contributor
    Posted Oct 25, 2022 05:11 PM
    Hi Ganesh,

    Based on your expected output, it doesn't seem like you want a rank, but rather an index for duplicate rows. If this is really what you want, I actually have no idea how to create it as a calculated column with DAX, but this is how I would do it in the query editor:

    let
      Source = Table.FromRows(
        Json.Document(
          Binary.Decompress(
            Binary.FromText(
              "i45WcgrwUdJRcnI0BJKeTsGO4WGGRgbBIbpGBgZKsTrRSo7Ozi5AKUewAkNDI10jY3NDsExUalJRfl5mMlA8ygkia6wLxMZgWZi5RjjMJWQvLtMNUV1lhO4qImRMgKSJhaWugaGBOaqMKboMiiuM0F2By40mFOuFhY4JuieQgw2nBIoXYBLmKBKxAA==",
              BinaryEncoding.Base64
            ),
            Compression.Deflate
          )
        ),
        let
          _t = ((type nullable text) meta [Serialized.Text = true])
        in
          type table [#"Org Name" = _t, #"Stockroom name" = _t, Product = _t]
      ),
      #"Grouped Rows" = Table.Group(
        Source,
        {"Org Name", "Stockroom name", "Product"},
        {{"Rank", each List.Numbers(1, Table.RowCount(_)), type list}}
      ),
      #"Expanded Rank" = Table.ExpandListColumn(#"Grouped Rows", "Rank"),
      #"Changed Type" = Table.TransformColumnTypes(#"Expanded Rank", {{"Rank", Int64.Type}})
    in
      #"Changed Type"​



    ------------------------------
    Tomas
    ------------------------------



  • 4.  RE: Rankx

    Silver Contributor
    Posted Oct 26, 2022 03:12 AM
      |   view attached
    Hi @Tomas Torp,

    Thanks for the information, somehow i have managed the ​and got result but not 100 %, so changed my logic using some dependent columns, again thanks for your time.

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------



  • 5.  RE: Rankx

    Silver Contributor
    Posted Oct 26, 2022 07:47 AM
    Hi @Tomas Torp,

    am trying to find first non-duplicate ​line in my table, why i mentioned this as rank because i can use 1 as my filter condition,
    and same product can be available in multiple org and stockroom, that is the reason i concatenate the org and stockroom in my calculation.

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------



  • 6.  RE: Rankx

    Bronze Contributor
    Posted Oct 26, 2022 09:18 AM
    As far as I know, there is no way with DAX to treat truly identical rows differently. That is why I said that I don't think this could be solved with DAX. Of course, if there are other columns in the table that make the rows unique, ranking them will be possible.

    ------------------------------
    Tomas
    ------------------------------



  • 7.  RE: Rankx
    Best Answer

    Posted Oct 27, 2022 09:37 PM
      |   view attached
    Olá

    Fiz com a linguagem M
    Veja se o anexo ajuda

    ------------------------------
    Vilmar Santos
    ------------------------------

    Attachment(s)

    pbix
    Rank.pbix   31 KB 1 version


  • 8.  RE: Rankx

    Silver Contributor
    Posted 24 days ago
    Hi @Vilmar Santos,

    Thank you very much for helping me, this has really worked in my project ​and this what am looking for very long, thanks a ton. ​​

    ------------------------------
    Ganesh Kumar
    Analyst
    ------------------------------