Number of Clients per responsible_user (Status can be the same during months)

[Katerina_SL]

Hello everyone. 

I would like to ask help in solving my issue.

I have a table in Power BI which contain following columns: ID, Client, Status, Stage, Time (when stage or status changed), Next time, responsible_user (screenshot is down).

Could you please help me to create the matrix which will contain in rows Responsible_user, in columns Status and Stage, values will be number of unique Clients. And the filter will be Years and Months. I can do it for dates which are in table but the problem appears when Status and Stage of Client is the same during several months. For example, Client 100 (screenshot down) has the Status Bronze from 05.06.2022 till 08.08.2022 (3 months). But July, when this Status is still the same, I can't include in result number of clients with Status Bronze in July. Please help me, how can I do it? 

For better understanding I attached the example of table and 2 examples of result for July and August 2022.

[123abc]

To create a matrix in Power BI that shows the number of unique clients for each responsible_user, status, and stage, considering that a status or stage can be the same for multiple months, you can use DAX (Data Analysis Expressions) to calculate the count of clients based on your date filters. Here's a step-by-step guide to achieving this:

1. Open Power BI and load your data table.

2. Create a new table with unique combinations of responsible_user, status, stage, and month. You can do this using the "Modeling" tab and the "New Table" option. Use the following DAX formula to create this table (let's call it "UserStatusStageMonth"):

UserStatusStageMonth =
SUMMARIZE(
'YourTableName',
'YourTableName'[responsible_user],
'YourTableName'[Status],
'YourTableName'[Stage],
'YourTableName'[Year],
'YourTableName'[Month]
)

Replace 'YourTableName' with the actual name of your table.

1. Now, create a matrix visual in your report with the following settings:

   • Rows: responsible_user
   • Columns: Status, Stage
   • Values: COUNTROWS('UserStatusStageMonth') (this will give you the count of unique clients)

2. To handle the date filters (Years and Months), you can use slicers. Add slicers for Year and Month in your report.

3. To ensure that you capture the count of clients for months where the status or stage remains the same, you can use a DAX measure to calculate the count of clients for a selected month. Create a new measure with the following DAX formula:

CountClientsForSelectedMonth =
VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
RETURN
CALCULATE(
COUNTROWS('UserStatusStageMonth'),
FILTER(
'UserStatusStageMonth',
'UserStatusStageMonth'[Year] = SelectedYear
&& 'UserStatusStageMonth'[Month] = SelectedMonth
)
)

This measure calculates the count of clients for the selected year and month using the slicers.

Now, when you select a specific year and month in your slicers, the matrix visual will display the number of unique clients for each responsible_user, status, and stage, including cases where the status or stage remains the same for multiple months.

Ensure that you have a date table ('Calendar') in your data model with the Year and Month columns for filtering purposes. If you don't have one, you can create it by importing a date table into your Power BI model or using DAX to generate one.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Katerina_SL]

Hello, Thank you very much for your rapid response. 

I tried your suggested method but it doesn't give me the expected result because it still doesn't calculate the clients which have the same status during several months.

I will try to tell you my thoughts regarding it. I hope I can explain better in this case:)

When we created the table UserStatusStageMonth with unique combinations, it calculated based on table where status is not updating (duplicate) each month when status are still the same. As in example, for client 100 with Status Bronze and empty Stage is the same from 05.06.2022 till 08.08.2022 (3 months). In my table it's referred only to June. And if I create the table UserStatusStageMonth of unique combinations from this table, it will not appear rows with months July and August for client 100 with Status Bronze and empty Stage because they just do not exist.

The measure CountClientsForSelectedMonth also doesn't calculate it because it count rows in calculated table UserStatusStageMonth which doesn't include for this example months July for Client 100 with status Bronze and empty Stage. 

And I need to get the result where for this example for Responsible_user 24 (who is responsible for Client 100) will be the number of Clients with Status Bronze and empty progress for June: 1, for July: 1 and also for August: 1 (and it's only Client 100).

I would be appreciate a lot if you would help me this issue. 

[123abc]

I understand your requirement better now. You want to count clients with the same status and stage, even if they persist over multiple months. To achieve this, you'll need to create a calculated table that summarizes the data as you need it. Here's a modified approach:

1. Data Transformation: Make sure you have a date table with year and month columns as previously mentioned.

2. Calendar Table: Create a calendar table with columns for Year and Month, if you haven't already.

3. Calculated Table: Create a calculated table using DAX. This table will summarize the data by responsible_user, status, stage, and month. Here's a sample DAX formula for your calculated table:

UserStatusStageMonth =
SUMMARIZE(
YourData,
YourData[Responsible_user],
YourData[Status],
YourData[Stage],
'Calendar'[Year],
'Calendar'[Month]
)

1. This DAX formula creates a new table with unique combinations of responsible_user, status, stage, year, and month.

2. Matrix Visualization: Create a matrix visualization and set the following fields:

   • Rows: "Responsible_user"
   • Columns: "Status" and "Stage"
   • Values: "Client" (Count of Unique Values)

3. Filters: Add the "Year" and "Month" columns from the calendar table as filters to the report page. These filters will allow users to select the desired year and month.

4. Slicer for Time: Create a slicer for the "Time" column to allow users to select a specific date range. This slicer is used for filtering the data within the selected time range.

Now, with this approach, when a user selects a year and month, the matrix will show the count of unique clients for each responsible user, broken down by status and stage, taking into account the specified time range. This should correctly handle the case where a status or stage persists over multiple months.

By creating a calculated table with unique combinations of responsible_user, status, stage, year, and month, you ensure that the matrix reflects the count of clients for each combination, even if the combination spans multiple months.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Katerina_SL]

Okay, I did it myself and got expected values. But I have problem in visualization now. I have correct values only when I'm using both filters year and month, but I want to have possibility to use them separetely. Could you please check my functiona and say what can be wrong?

Num_Clients =

VAR Current_month = SELECTEDVALUE(Calendar[Month])

VAR Current_year = SELECTEDVALUE(Calendar[Year])

VAR Current_user = SELECTEDVALUE('user'[id])

Return

CALCULATE(

    DISTINCTCOUNT('MainTable'[Client]),

    FILTER('MainTable',

        (

            Current_user = 'MainTable'[responsible_user]

        )

        &&

        (

            (

                Current_year = 'MainTable'[Time_year] &&

                Current_month >= 'MainTable'[Time_month] &&

                    (

                        (Current_year = 'MainTable'[Next Time_year] &&

                        Current_month <= 'MainTable'[Next Time_month]) ||

                        (Current_year < 'MainTable'[Next Time_year]) ||

                        (ISBLANK('MainTable'[Next Time]))

                    )

            )

            ||

            (

                Current_year > 'MainTable'[Time_year] &&

                    (

                        (Current_year = 'MainTable'[Next Time_year] &&

                        Current_month <= 'MainTable'[Next Time_month]) ||

                        (Current_year < 'MainTable'[Next Time_year]) ||

                        (ISBLANK('MainTable'[Next Time]))

                    )

            )

        )

    )

)

[Katerina_SL]

Thank you for your respond. I tried to use your update variant but still can't get expected result.

1. When I changed the function for calculated table, it has the same number of rows as before. That's why I don't understand who this change could influence.

2. Also could you explain me please how can I add to values in matrix this:

• Values: "Client" (Count of Unique Values)

I tried to do it different variants. First, I just add Client_id to calculated table UserStatusStageMonth with unique combinations and just put this number as Value in matrix, but in this case I can't use filters from Calendar (there is no any reaction if I'm changing them, relationship between table Calendar and UserStatusStageMonth can't create, because only Years and Months are the same).

Second varian, I didn't add Client_id to calculated table UserStatusStageMonth. I add to Values in matrix just client_if from my original table, but in this case all number for different clients/status/stage are absolutely the same.

[123abc]

I apologize for the confusion in my previous responses. Based on your further explanation, it seems you need to achieve this result differently.

You want to count the number of unique clients based on the selected month and conditions, including the continuity of status. Here's an updated approach:

1. Create a 'MonthlySnapshots' Table: Similar to previous steps, create a table with unique combinations of 'Responsible_user', 'Status', and 'Stage' for each month.

2. DAX Measure for Count of Unique Clients: Create a new DAX measure to calculate the count of unique clients based on the selected month. This measure will calculate the count for each combination of 'Responsible_user', 'Status', and 'Stage' for the selected month. Use the following DAX measure:

CountUniqueClients =
VAR SelectedMonth = MAX('DateTable'[Year]) & MAX('DateTable'[Month])
RETURN
CALCULATE(
DISTINCTCOUNT('YourTable'[Client]),
FILTER(
ALL('YourTable'),
'YourTable'[Responsible_user] = SELECTEDVALUE('MonthlySnapshots'[Responsible_user]) &&
'YourTable'[Status] = SELECTEDVALUE('MonthlySnapshots'[Status]) &&
'YourTable'[Stage] = SELECTEDVALUE('MonthlySnapshots'[Stage]) &&
'YourTable'[YearMonth] <= SelectedMonth
)
)

1. Make sure you replace 'YourTable' with the name of your original data table and 'YearMonth' with the appropriate column that combines the year and month.

2. Matrix Visualization:

   • Create a matrix visualization in Power BI.
   • Put 'Responsible_user' in the Rows area.
   • Put 'Status' and 'Stage' in the Columns area.
   • Place the 'CountUniqueClients' measure in the Values area.

Now, the 'CountUniqueClients' measure calculates the count of unique clients based on the selected month and considers the continuity of status and stage. You can add this measure to the matrix to get the desired result.

If you still face issues with this setup or if you need additional assistance, please provide more details about your data model and any specific problems you encounter, and I'll be happy to help further.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.