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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
uir
Regular Visitor

Need help - how to rank based on filter and group by specific column?

Many thanks for your help in advance

raw data is like the following:

uir_1-1638928871674.png

 

after setting the filter: purchase date between 202108-202110, how can I get the following result?

(filter data that purchase date between 202108-202110, and rank by customerID)

uir_2-1638928899079.png

 

 

1 ACCEPTED SOLUTION

@uir , My bad,

You needed coutrows

countrows(filter(allselected(Table), Table[Customer] = max(Table[Customer ID]) && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )

View solution in original post

6 REPLIES 6
smpa01
Super User
Super User

@uir  can you try this measure?

Rank =
RANKX (
    FILTER ( ALLSELECTED ( tbl ), tbl[custID] = MAX ( tbl[custID] ) ),
    [_maxPurchaseDate],
    ,
    ASC
)

 

smpa01_0-1639112370196.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
v-zhangti
Community Support
Community Support

Hi, @uir 

 

You can try the following methods.

Measure:

Rank = 
CALCULATE (
    COUNT ( 'Table'[CustomerID] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [CustomerID] = MAX ( 'Table'[CustomerID] )
            && [Purchase date] <= MAX ( 'Table'[Purchase date] )
    )
)

vzhangti_0-1639105213518.png

Is this the output you expect?

vzhangti_1-1639105264651.pngvzhangti_2-1639105306220.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

 

TomMartens
Super User
Super User

Hey @uir ,

 

creating maeasure like this, requires a data model instead of a single table. For this reason i created two additional tables:

  • Customer and
  • Date

I created relationships between the new tables and the existing one (the table that contains the sample data). I called the existing table: fact. The screenshot below shows the data model:

image.png

Then I created the measure below:

 

Measure = 
var outerGroupElement = MAX( 'Customer'[CustomerID] )
var innerGroupTable = CALCULATETABLE( VALUES( 'Fact'[Purchase Date] ) , ALLSELECTED( 'Date'[Date] ) )
var __t =  
        GENERATE(
            VALUES( 'Customer'[CustomerID] )
            , innerGroupTable
        )
var __t1 =
    ADDCOLUMNS(
        __t
        , "rk" 
            , rankx( __t
                , CALCULATE( MAX('Fact'[Purchase Date] ) 
                    , ALL( 'Date' ) 
                ) 
                , 
                , ASC 
            )
    )
return
IF( MAX( 'Date'[Date] ) in innerGroupTable
    , var cid = MAX( 'Customer'[CustomerID] )
    var pd = MAX( 'Date'[Date] )
    return
    MAXX( FILTER( __t1 , [CustomerID] = cid && [Purchase Date] = pd) , [rk] )
    , BLANK()
)

 

This allows to create a simple report like below:

image.png

Be aware that the visuals (slicer, table) are using columns from the new tables instead of the columns from the old table.

 

Hopefully, this provides some new ideas on how to tackle your challenge.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
amitchandak
Super User
Super User

@uir , You can use filter on purchase date

or create this into date and use a filter on connected date table

date = date(left([purchase Date],4), right([purchase date],2) ,1) 

 

You can not get that with Rank,

So create a measure

countx(filter(allselected(Table), Table[Customer] =  max(Table[Customer ID])   && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )

countx need 2 parameters, however, it seems that your measure only have 1, could u plz share the 2nd parameter?

@uir , My bad,

You needed coutrows

countrows(filter(allselected(Table), Table[Customer] = max(Table[Customer ID]) && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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