Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hello everybody,
I've got a table like:
id | timestamp | uid | tariff_name |
1 16.01.2017 id001 tariff_A
2 16.01.2017 id002 tariff_A
3 17.01.2017 id001 tariff_B
4 18.01.2017 id002 tariff_C
5 18.01.2017 id003 tariff_A
How can i count: all purchases for id001 made after purchasing tariff_A ? (1 purchase for example table)
sum of id that purchase smth after purchasing tariff_A? (2 (id001 & id002 id for example table)
hi @Anonymous
you could do a calc column like this
CntLaterPurchases = CALCULATE( COUNTROWS( FILTER( table, AND( table[uid] = EARLIER(table[UID]), table[id] > EARLIER(table[id]) ) ) ) )
HTH,
Frank
hi @BetterCallFrank thanks for reply, i've got the following error: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist", what can i do wrong?)
And what about if the first purchase was tariff_B, then tariff_A, and then tariff_C - i need to count only tariff_C - is it possible with your suggestion?
Hi @Anonymous
for your first question, try this measure:
Cnt Later Purchase of Customer:=
IF( HASONEVALUE(Table1[uid] ), VAR TheUID = VALUES( Table1[uid] ) VAR TheID = MAX( Table1[id] ) RETURN COUNTROWS( FILTER( ALL( Table1 ), AND( Table1[uid] = TheUID, Table1[id] > TheID ) ) ) )