Forum Discussion
Can you make Rankx look at variable table
- 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)RETURNSWITCH(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!
Well I think I pulled it off with this code 😁:
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!