Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Many thanks for your help in advance
raw data is like the following:
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)
Solved! Go to Solution.
@uir , My bad,
You needed coutrows
countrows(filter(allselected(Table), Table[Customer] = max(Table[Customer ID]) && Table[Purchase Date] <= Max(Table[Purchase Date]) ) )
@uir can you try this measure?
Rank =
RANKX (
FILTER ( ALLSELECTED ( tbl ), tbl[custID] = MAX ( tbl[custID] ) ),
[_maxPurchaseDate],
,
ASC
)
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] )
)
)
Is this the output you expect?
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.
Hey @uir ,
creating maeasure like this, requires a data model instead of a single table. For this reason i created two additional tables:
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:
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:
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
@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]) ) )
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |