How to group and Index a Union table?

DAX DAX Calculations
1

Hi Guys,

My challenge, I want to find a way to group and create an index on my traveller number based on its SAP number. I want the index from the lowest traveller number in each group

Example: note all traveller numbers are unique never repeated

Capture
Capture1206×458 87.3 KB

I have been desperately trying to group these using DAX.

I had to union two tables together in order to improve my data refresh times as the historic table is over 50 million rows of data (Condensed to upload here) only the SQ00 file is refreshed

My ultimate goal is to create a matrix table that shows SAP in the row axis and index numbers in the columns then as I place the DAX I’m requesting here into the values I will have a nicely arranged table of traveller numbers filtered by index number.

Appreciate your help as always.

PBIX attached

EDNA GROUPS.pbix (2.8 MB)

2

I tried this in a calculated column as I saw it on Youtube

https://www.google.com/search?q=group+and+index+in+dax&rlz=1C1BNSD_enAE977AE977&oq=group+and+index+in+dax&aqs=chrome.0.69i59j0i22i30j69i60j69i61.5219j0j7&sourceid=chrome&ie=UTF-8#kpvalbx=_Aa4xYrr7KJb5tAaUo5ZI13

But it didn’t quite work but its close ! basically the lowest traveler number for each SAP number should be index 1 then next largest 2 and so on. see screen shot its not quite there.

Capture
Capture1277×700 78.5 KB 

Index = 
CALCULATE( DISTINCTCOUNT( 'SQ00 Confirmations'[Traveler Number]),
FILTER('SQ00 Confirmations',
'SQ00 Confirmations'[SAP Number] = EARLIER( 'SQ00 Confirmations'[SAP Number] ) && 
'SQ00 Confirmations'[Traveler Number] <= 'SQ00 Confirmations'[Traveler Number] ) )
3

Hi @Krays23,
If what you need is an index that starts for each SAP Number and that is assigned according to the Traveler Number, you have almost done it, I think you are missing an EARLIER in the last condition:

Index = CALCULATE(
DISTINCTCOUNT( ‘SQ00 Confirmations’[Traveler Number] ),
FILTER(
‘SQ00 Confirmations’,
‘SQ00 Confirmations’[SAP Number] =
EARLIER( ‘SQ00 Confirmations’[SAP Number] ) &&
‘SQ00 Confirmations’[Traveler Number] <= EARLIER( ‘SQ00 Confirmations’[Traveler Number] )
)
)

Greetings

4

Hi @Krays23, we’ve noticed that no response has been received from you since a few days ago.

We just want to check if you still need further help with this post?

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @Krays23, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.