Forum Discussion
RANKING ON DATES AND 2 GROUPS
Hello,
I'm new to DAX and I'm trying to index dates chronologically, grouped by customers and contracts. This index must respect the filters.
Exemple :
I have a dataset with columns CLIENT, CONTRAT and DATE and possible filter on this 3 column
I want to add an index column with exepted result like :
Can someone help me please ?
Regards,
Max
5 Replies
- foodd
Community Champion
The reply was created from a phone and is untested. An explanation of the general pattern and a
the sample is below. You will need to test and adjust the measure where needed. The key is RANKX
which you can research and find tutorials on for further use.
To create an index of dates chronologically, grouped by customers and contracts, while respecting filters, you can use the RANKX function in DAX. The RANKX function allows you to rank values based on a specific expression within a context. In this case, we'll use RANKX to rank the dates for CLIENT and CONTRAT.
Index Chronological =
RANKX(
FILTER(ALL('YourTableName'),
NOT(ISBLANK([DATE])) // Apply the necessary filters to exclude blank dates
),
[DATE],
,
ASC,
Dense
)Hello,
Thank you for your fast answer but it dont works.
I have an error because the column DATE dont contains unique value.
- foodd
A date table in Power BI needs to be unique because it serves as the foundation for date-based calculations and relationships within the data model. When creating a date table, it should consist of unique and continuous dates, typically covering a range of dates without any duplicates or gaps.
I understand the importance of having unique dates but in my case they are not and it is for this reason that I cannot index my dates and that I created this post. This data represents insurance claims. A customer may have a claim on an insurance contract on the same date as another customer or on another contract. Is there a way to circumvent the problem?
- foodd
Hypothetically, adding your 'Claim number' (and if it is a combination of claim number + indicator + Claimant ID for processing step or change (if necessary) would allow for a unique identifier in the queries). You will need to test and confirm any relationships and filtering within the model.
