topic Re: Get first and last status of a member in DAX Commands and Tips

Get first and last status of a member

Ossy81 — Mon, 14 Nov 2022 20:35:12 GMT

Working on a report to show how many members moved between different statuses. How many from:

A -> B

B->A

A->C

within a selected period. So it could be that member went from A->B and than from B->C but for the report it should be registered as a member moved from A->C

I used this formula:

 VAR MinDate = CALCULATE ( MIN ( 'F - Transaction ML'[d_date] )
, NOT ( ISBLANK ( 'F - Transaction ML'[d_member] ) )
, ALLSELECTED ( 'F - Transaction ML'[d_date] ) ) 

VAR Weekstartdate = MinDate - WEEKDAY ( MinDate, 2 ) + 1 

VAR FirstMemberStatus = CALCULATE ( MIN ( 'F - Transaction ML'[Member Status] )
, ALLEXCEPT ( 'F - Transaction ML', 'D - Calendar' )
, FILTER ( 'F - Transaction ML'
, 'F - Transaction ML'[d_date] >= Weekstartdate ) ) 

RETURN FirstMemberStatus

And created a similar one for the last status, but then with MAX. When showing it on a member level it works fine, but i need to know "total number of members"/count distinct from status A->B , A->C etc...

Who could guide me how to solve this?I need somehow this calc to be always on a member level

Re: Get first and last status of a member

amitchandak — Tue, 15 Nov 2022 01:46:12 GMT

Ossy81 , refer if these blogs can help

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e

For first use min

Re: Get first and last status of a member

Anonymous — Tue, 15 Nov 2022 05:54:43 GMT

Hi Ossy81 ,

Do you wnat to get the number of members which the status switch from one to another? In order to get a better understanding on your requirement and give you a suitable solution, could you please provide some sample data in the table 'F - Transaction ML' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

Re: Get first and last status of a member

Ossy81 — Tue, 15 Nov 2022 11:30:32 GMT

I'm afraid this is not going to work 😞 We want to know how many members moved van status A-> B and from A-> C

I already had a result which shows per member the 1st and the last status:

But when i do a count distinct by these statuses it doesn't work:

So the result what i want is: