Power BI - Dynamic rank leads to cartesian when second table added to table visualization

user_994771 0
Viewed 150

Power BI Desktop Version: 2.97.861.0 64-bit (September 2021)

I am attempting to create a dynamic rank in a table visualization of a Power BI Report that recalculates based on the filter the user applies

This is my current DAX formula…

Rank = 
VAR v_rank =

RANKX(
    ALLSELECTED('cust_income'),
    CALCULATE(SUM('cust_income'[Income])),
    ,
    DESC,
    Dense
    )

RETURN  
v_rank

Screenshot below is filtered for Customer ID = 1

The rank works as expected - it is specific to the Customer ID = 1 records

HOWEVER - as soon as I add Product Desc to the table visualization, I see the strange cartesian result on the right…

cartesian

There are only two tables. The relationship is a very basic 1:N using Product Code...

erd

How to keep Product Desc in my table visualization but eliminate the nonsensical rows that have no Income value?

Thank you for your insights

dax powerbi data-visualization
1 Answers

Could you try:

Rank = 
VAR v_rank =
IF (
    HASONEVALUE(cust_income[Income]),
    RANKX(
        ALLSELECTED('cust_income'),
        CALCULATE(SUM('cust_income'[Income])),
        ,
        DESC,
        Dense
    )
)

RETURN  
v_rank
user_122426250
Related