cancel
Showing results for
Did you mean:
Helper I

## Get first and last status of a member

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

3 REPLIES 3
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Helper I

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:

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors