Create a Rank measure with multiple conditions and group by

[yellow43]

Hi Fabric Community,

I need help with following:

Have a Fact Table with following measures: avg_sales, qty_by_customer.
Have 3 different Dimension Tables: Customer, Product, Country

Need to create a rank where I have following conditions:
1. same customer, same country, different product should rank by AVG_SALES (1st criteria) and 2nd criteria QTY_BY_CUSTOMER
2. ignore all null values and zero values in measure qty_by_customer and avg_sales.

DIM Table Customer

CustomerID	Customer Name
1	Alex
2	Samantha

DIM Table Country

ISO Code	Country
US	United States
IT	Italy
ES	Spain
FR	France

DIM Table Product

ProductID	Product
A	MA-1
B	MA-2
C	BD-1

Output: 

CustomerID	ISO Code	Product	[qty_by_customer]	[avg_sales]	RANK (new measure)
1	US	B	30	14€	1
1	US	A	100	15€	2
1	IT	C	50	15€	1
1	IT	A	30	30€	2
1	ES	C	60	14€	1
1	ES	A	30	16€	2
1	ES	B	35	16€	3

Can you help me?

@DallasBaba last time you helped me, but problem now is that I want a measure. 

Thank you in advance. 

[yellow43]

Hi @DallasBaba , 

It's not working. 

When I replace VAR for my Fact Table, it only shows Measures. It does not show Columns. 

Let me tell you that I have a DirectQuery Connection. This might be an important issue. 

Can you help me?

Thank you in advance!  

[DallasBaba]

@yellow43 Can you share a .pbix file with sample dataset for solution debugging.

[v-shex-msft]

HI @yellow43,

AFAIK, direct query should limit on the calculate field and dax functions usages.

Perhaps you can consider to duplicate a table and switch it to import mode, then these part should be worked with the dax expressions that @DallasBaba  shared. (you report will use composite model to work with both import and direct query connections)

Use composite models in Power BI Desktop - Power BI | Microsoft Learn
Regards,

Xiaoxin Sheng

[DallasBaba]

@yellow43 Can you create a calculated column in the Fact Table that computes the ranking based on the specified criteria. 

_Ranking = 
VAR CustomerID = 'Fact Table'[CustomerID]
VAR CountryTable = 'Fact Table'[ISO Code]
VAR ProductTable = 'Fact Table'[Product]
VAR AvgSales = 'Fact Table'[avg_sales]
VAR QtyByCustomer = 'Fact Table'[qty_by_customer]

RETURN
IF(
    NOT(ISBLANK(AvgSales)) && NOT(ISBLANK(QtyByCustomer)) && AvgSales > 0 && QtyByCustomer > 0,
    COUNTROWS(
        FILTER(
            'Fact Table',
            'Fact Table'[CustomerID] = CustomerID &&
            'Fact Table'[ISO Code] = CountryTable &&
            'Fact Table'[Product] <> ProductTable &&
            NOT(ISBLANK('Fact Table'[avg_sales])) && 'Fact Table'[avg_sales] > 0 &&
            NOT(ISBLANK('Fact Table'[qty_by_customer])) && 'Fact Table'[qty_by_customer] > 0 &&
            'Fact Table'[qty_by_customer] >= QtyByCustomer
        )
    ) + 1,   // Adding +1 to ensure the ranking does not return BLANK as 0
    BLANK()
)

I hope this meet your need. See Attach pbix file for details.

Note: 

If this post is helpful, please give my response a thumbs up! You can also mark it as the solution to help others find it easily.

[littlemojopuppy]

@yellow43 

Either I'm missing something or the RANK() function should give you exactly what you need