Forum Discussion

rpinxt's avatar
rpinxt
Icon for Solution Sage rankSolution Sage
3 years ago
Solved

Can you make Rankx look at variable table

I have this variable:

VAR rank_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)
 
Now the part " 'Backorder Details'[Origin]) " I want to make variable because I am using a field parameter to switch the break down.
 
I made a measure called "Test" (for now) which gives back the field of my selection:
VAR veld = SELECTEDVALUE(prmBreakdown[prmBreakdown Fields])
 
This works as you can see in a card visual:

 

So the question now.....can I make it so, that in the VAR rank_qty I swap the fixed text "'Backorder Details'[Origin])" with the variable outcome of measure Test?

I cannot put in the measure directly and I can also not use the dax code of measure Test directly in the VAR rank_qty (as I have it now)

 

Would there be a way to make this work?

Need Help
  • rpinxt's avatar
    rpinxt
    3 years ago

    Well I think I pulled it off with this code 😁:

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)
    VAR rank_org_usd = RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC)

    VAR rank_pom_qty = RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC)
    VAR rank_pom_usd = RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC)

    VAR rank_pf_qty = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC)
    VAR rank_pf_usd = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC)

    RETURN

    SWITCH(TRUE(),
        selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
        selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
        selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
        selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
        selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
        selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    But does anybody know how I can alter the code so it does not als rank the totals??

     

    *EDIT: I found out! 😁
    Did it in declaring the VARs :

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC))
    VAR rank_org_usd = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC))

    VAR rank_pom_qty = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC))
    VAR rank_pom_usd = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC))

    VAR rank_pf_qty = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC))
    VAR rank_pf_usd = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC))

    RETURN

    SWITCH(TRUE(),
    selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
    selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
    selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
    selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
    selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
    selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    Thanks to OwenAuger for putting me on the right track!

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    3 years ago

    Hi rpinxt 

    I believe the only way to get this to work is to use the SWITCH function (or IF) to select the appropriate rank calculation based on the field parameter selection.

    This would mean enumerating all possible selections for the field parameter within the arguments of the SWITCH function.

    It would look something like this:

     

    VAR breakdown_selection =
    SELECTEDVALUE ( prmBreakdown[prmBreakdown Fields] )
VAR rank_qty =
    SWITCH (
        breakdown_selection,
        "'Backorder Details'[Origin]",
        RANKX( ALLSELECTED ( 'Backorder Details'[Origin] ), [BO Qty], , DESC ),
        "'Backorder Details'[Other Column 1]",
        RANKX( ALLSELECTED ( 'Backorder Details'[Other Column 1] ), [BO Qty], , DESC ),
        "'Backorder Details'[Other Column 2]",
        RANKX( ALLSELECTED ( 'Backorder Details'[Other Column 2] ), [BO Qty], , DESC )
    )

     

    and so on.

    This code would need to be updated if the set of columns available for selection within this field parameter changed.

     

    There's no way to swap out the text within a DAX expression to change the column reference unfortunately (a bit like how INDIRECT might be used in Excel).

     

    Regards,

    Owen

    • rpinxt's avatar
      rpinxt
      Icon for Solution Sage rankSolution Sage
      3 years ago

      Thanks OwenAuger .

      And indeed INDIRECT of Excel would be somewhat the equivalent in Power BI what I was looking for.

      Too bad something cannot be done (yet) in Power BI.

       

      Specially with the new feature of Field Parameters this would be very welcome.

      I made these VARs I mentioned already to Rank the outcome correctly when either Quantities or USD where chose (also a Field Parameter).

      However they where fixed on 1 breakdown and for now we have 3.

       

      So I guess my Switch would be then 6?

       

      This is my working code now:

      Rank Measure =
      VAR selected = SELECTEDVALUE(prmMeasure[Measure2])

      VAR rank_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)
      VAR rank_usd = RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC)

      RETURN
      IF(
          HASONEVALUE('Backorder Details'[Origin]),SWITCH(selected,"BO Qty",rank_qty,"USD Amt",rank_usd)
      )
       
      So then I think VARs would be like: rank_Origin_qty, rank_Origin_usd and that 3 times.
       
      Am just wondering what my return would look like...?
      Because selected is Qty or USD so then I would also need a selected2 being Origin, Val2 and Val3.
  • rpinxt's avatar
    rpinxt
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Well I think I pulled it off with this code 😁:

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC)
    VAR rank_org_usd = RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC)

    VAR rank_pom_qty = RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC)
    VAR rank_pom_usd = RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC)

    VAR rank_pf_qty = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC)
    VAR rank_pf_usd = RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC)

    RETURN

    SWITCH(TRUE(),
        selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
        selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
        selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
        selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
        selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
        selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    But does anybody know how I can alter the code so it does not als rank the totals??

     

    *EDIT: I found out! 😁
    Did it in declaring the VARs :

    Rank Measure =
    VAR selected = SELECTEDVALUE(prmMeasure[Measure2])
    VAR selected2 = SELECTEDVALUE(prmBreakdown[Breakdown])

    VAR rank_org_qty = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[BO Qty],,DESC))
    VAR rank_org_usd = IF( HASONEVALUE('Backorder Details'[Origin]),RANKX(ALLSELECTED('Backorder Details'[Origin]),[USD Amt],,DESC))

    VAR rank_pom_qty = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[BO Qty],,DESC))
    VAR rank_pom_usd = IF( HASONEVALUE('Backorder Details'[POM]),RANKX(ALLSELECTED('Backorder Details'[POM]),[USD Amt],,DESC))

    VAR rank_pf_qty = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[BO Qty],,DESC))
    VAR rank_pf_usd = IF( HASONEVALUE('Backorder Details'[PF Name]),RANKX(ALLSELECTED('Backorder Details'[PF Name]),[USD Amt],,DESC))

    RETURN

    SWITCH(TRUE(),
    selected = "BO Qty" && selected2 = "Origin", rank_org_qty,
    selected = "USD Amt" && selected2 = "Origin", rank_org_usd,
    selected = "BO Qty" && selected2 = "POM", rank_pom_qty,
    selected = "USD Amt" && selected2 = "POM", rank_pom_usd,
    selected = "BO Qty" && selected2 = "Prod.Fam. Name", rank_pf_qty,
    selected = "USD Amt" && selected2 = "Prod.Fam. Name", rank_pf_usd
    )

     

    Thanks to OwenAuger for putting me on the right track!