Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
rpinxt
Impactful Individual
Impactful Individual

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:
rpinxt_0-1666348859799.png

 

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?

1 ACCEPTED SOLUTION
rpinxt
Impactful Individual
Impactful Individual

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
)
rpinxt_0-1666363329408.pngrpinxt_1-1666363347873.pngrpinxt_2-1666363383294.pngrpinxt_4-1666363728659.png

 

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!

View solution in original post

3 REPLIES 3
rpinxt
Impactful Individual
Impactful Individual

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
)
rpinxt_0-1666363329408.pngrpinxt_1-1666363347873.pngrpinxt_2-1666363383294.pngrpinxt_4-1666363728659.png

 

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!

OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
rpinxt
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.