Forum Discussion

liselotte's avatar
liselotte
Icon for Advocate I rankAdvocate I
2 years ago
Solved

Rank a measure

I have
- a dimension table 'dim_Date' with column 'Date'

- a dimension table 'dim_Customers' with columns 'Customer ID', 'Customer Name'

- a fact table 'fact_Sales' with columns 'Customer ID', 'Date', 'Type', 'Sales'.

 

I want to create a measure to rank Customers based of their Sum of Sales over a time period which is filtered in a slicer of 'dim_Date'['Date']. This measure will be used later in a visual graph with visual filter on 'fact_Sales'['Type']. 

This is my DAX query for the measure:

 

Rank = RANKX(ALLSELECTED('fact_Sales'),CALCULATE(SUM('fact_Sales'[Sales]),ALLEXCEPT('fact_Sales','fact_Sales'[Customer ID])),,DESC,Dense)

 

 But the function ALLACCEPT in my measure ignore filters of 'dim_Date'['Date'] and 'fact_Sales'['Type'].

Could you help me with changing the query so that it doesn't ignore filters of 'dim_Date'['Date'] and 'fact_Sales'['Type'], please? Thank you 🙂