How Can I Create a RANKX Measure Without Triggering a Crossjoin?

[Hamzehn]

Hi, I have two tables Companies and Transactions that have a 1-to-many company-to-transaction relationship and are linked in the model by a Company ID field.

The Companies table has the following fields:

1. Company ID
2. Company Name

While the Transactions table has the following fields (note the fund field in particular - a single company can have multiple transactions with 1 or more fund(s)):

1. Transaction ID
2. Related Company ID
3. Date
4. Fund
5. Amount

I'm trying to create a measure (DateRank) that ranks the transactions by date in each Company-Fund group and then display that in a table that shows the Company Name, Fund, Transaction ID, and calculated rank.

I haven't been able to do this without ending up with a Cartesian product of the two tables where the rank is calculated correctly for rows where the Companies[Company ID] matches the Transactions[Related Company ID], but the table also ends up having extra rows for all items in the Transactions table (even the ones where the company id's don't match) with the calculated value being 1.

As part of my research online I found a bunch of blogs (example) that talk about DAX auto-exist resulting in behavior like this, but while they did a really good job of explaining why that behavior was happening I couldn't find in them any solution that worked for my use case.

I would really appreciate someone's help here as there must be some way to do this!

Here is the formula that I have so far:

Measure DateRank =
    VAR thisFund = SELECTEDVALUE(Transactions[Fund])
    VAR thisDate = SELECTEDVALUE(Transactions[Date])
    Return
        CALCULATE(
            RANKX(
                ALLSELECTED(Transactions),
                Transactions[Date],
                thisCloseDate,
                DESC
            ),
            KEEPFILTERS(Transactions[Fund] = thisFund)
        )

[amitchandak · Accepted solution]

@Hamzehn , if you need rank on date in this table this can be column not measure

rank = rankx(filter(Table, [related company id] = earlier( [related company id] )  && [Fund] =earlier(Fund) ), [Date],,desc, dense)

You can have measure rank on max date

example

rank = rankx(filter(Table, [related company id] = max( [related company id] ) ), calculate(max([Date])),,desc, dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[v-yetao1-msft · Accepted solution]

Hi @Hamzehn 

Is this the result you want ? Group by Related Company ID and Fund , rank by Date .

You can create a column to return rank .

rank = rankx(filter(Transactions, [related company id] = earlier( [related company id] ) && [Fund] =earlier(Transactions[Fund]) ), [Date],,ASC, dense)

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Hamzehn , if you need rank on date in this table this can be column not measure

rank = rankx(filter(Table, [related company id] = earlier( [related company id] )  && [Fund] =earlier(Fund) ), [Date],,desc, dense)

You can have measure rank on max date

example

rank = rankx(filter(Table, [related company id] = max( [related company id] ) ), calculate(max([Date])),,desc, dense)

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Hamzehn]

Thanks @amitchandak. I think you're right and I may not need a measure exactly to do rankings. For my purpose, since this was going to be an intermediate step of what I was trying to do, I figured I may even get away without having to do the rank by just relying on the date column that's already there.