The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi All
I want to create a column with a rank or row number grouo by date and customer number.
I don't need a special order by column because i will only extract 1 values from the created column.
Date, Customer_number columns are what i have and Rank / Rownumber column is what i expected
Can i do this with Dax??
Thanks in advance 🙂
Solved! Go to Solution.
@miinseon - This can be done with DAX, but first you must add an Index column in Power Query.
Follow these instructions to do this: https://support.microsoft.com/en-gb/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-b...
Once you have done this, the following DAX will work as a Calculated Column or Measure - you just need to change the table names:
VAR _index =
CALCULATE ( MAX ( 'Table (6)'[Index] ) )
VAR _customer =
CALCULATE ( MAX ( 'Table (6)'[Customer] ) )
VAR _date =
CALCULATE ( MAX ( 'Table (6)'[Date] ) )
VAR _tmptable1 =
FILTER (
ALL ( 'Table (6)' ),
'Table (6)'[Customer] = _customer
&& 'Table (6)'[Date] = _date
&& 'Table (6)'[Index] < _index
)
VAR _tmptable2 =
ADDCOLUMNS (
_tmptable1,
"@diff",
'Table (6)'[Index]
- MAXX (
FILTER (
ALL ( 'Table (6)' ),
'Table (6)'[Index] < EARLIER ( 'Table (6)'[Index] )
&& 'Table (6)'[Customer] = EARLIER ( 'Table (6)'[Customer] )
&& 'Table (6)'[Date] = EARLIER ( 'Table (6)'[Date] )
),
'Table (6)'[Index]
)
)
VAR _max =
MAXX ( _tmptable2, 'Table (6)'[Index] )
VAR _maxstart =
MAXX ( FILTER ( _tmptable2, [@diff] > 1 ), 'Table (6)'[Index] )
VAR _tmptable3 =
FILTER ( _tmptable2, 'Table (6)'[Index] >= _maxstart )
RETURN
IF (
ISBLANK ( _max ),
1,
IF ( _max = _index - 1, COUNTROWS ( _tmptable3 ) + 1, 1 )
)
Screenshot shows it's working for me:
If this works for you, please mark it as the solution
@miinseon - This can be done with DAX, but first you must add an Index column in Power Query.
Follow these instructions to do this: https://support.microsoft.com/en-gb/office/add-an-index-column-power-query-dc582eaf-e757-4b39-98e6-b...
Once you have done this, the following DAX will work as a Calculated Column or Measure - you just need to change the table names:
VAR _index =
CALCULATE ( MAX ( 'Table (6)'[Index] ) )
VAR _customer =
CALCULATE ( MAX ( 'Table (6)'[Customer] ) )
VAR _date =
CALCULATE ( MAX ( 'Table (6)'[Date] ) )
VAR _tmptable1 =
FILTER (
ALL ( 'Table (6)' ),
'Table (6)'[Customer] = _customer
&& 'Table (6)'[Date] = _date
&& 'Table (6)'[Index] < _index
)
VAR _tmptable2 =
ADDCOLUMNS (
_tmptable1,
"@diff",
'Table (6)'[Index]
- MAXX (
FILTER (
ALL ( 'Table (6)' ),
'Table (6)'[Index] < EARLIER ( 'Table (6)'[Index] )
&& 'Table (6)'[Customer] = EARLIER ( 'Table (6)'[Customer] )
&& 'Table (6)'[Date] = EARLIER ( 'Table (6)'[Date] )
),
'Table (6)'[Index]
)
)
VAR _max =
MAXX ( _tmptable2, 'Table (6)'[Index] )
VAR _maxstart =
MAXX ( FILTER ( _tmptable2, [@diff] > 1 ), 'Table (6)'[Index] )
VAR _tmptable3 =
FILTER ( _tmptable2, 'Table (6)'[Index] >= _maxstart )
RETURN
IF (
ISBLANK ( _max ),
1,
IF ( _max = _index - 1, COUNTROWS ( _tmptable3 ) + 1, 1 )
)
Screenshot shows it's working for me:
If this works for you, please mark it as the solution