Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
yellow43
Helper I
Helper I

Create a Rank measure with multiple conditions and group by

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

CustomerIDCustomer Name
1Alex
2Samantha

 

DIM Table Country

ISO CodeCountry
USUnited States
ITItaly
ESSpain
FRFrance

 

DIM Table Product

ProductIDProduct
AMA-1
BMA-2
CBD-1

 

Output: 

CustomerIDISO CodeProduct[qty_by_customer][avg_sales]RANK (new measure)

1

USB3014€1
1USA10015€2
1ITC5015€1
1ITA3030€2
1ESC6014€1
1ESA3016€2
1ESB3516€3

 

Can you help me?

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

 

Thank you in advance. 

5 REPLIES 5
yellow43
Helper I
Helper I

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!  

 

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

Thanks
Dallas

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
DallasBaba
Super User
Super User

@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()
)

 

 

 

DallasBaba_2-1712889331535.png

 

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.

 

Thanks
Dallas
littlemojopuppy
Community Champion
Community Champion

@yellow43 

 

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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.