topic Re: Create a Rank measure with multiple conditions and group by in DAX Commands and Tips

Create a Rank measure with multiple conditions and group by

yellow43 — Wed, 10 Apr 2024 14:41:14 GMT

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.

Re: Create a Rank measure with multiple conditions and group by

littlemojopuppy — Wed, 10 Apr 2024 19:27:57 GMT

yellow43

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

Re: Create a Rank measure with multiple conditions and group by

DallasBaba — Fri, 12 Apr 2024 04:37:06 GMT

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.

Re: Create a Rank measure with multiple conditions and group by

yellow43 — Fri, 12 Apr 2024 13:46:40 GMT

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!

Re: Create a Rank measure with multiple conditions and group by

Anonymous — Fri, 19 Apr 2024 08:28:15 GMT

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

Re: Create a Rank measure with multiple conditions and group by

DallasBaba — Fri, 19 Apr 2024 18:28:20 GMT

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