cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculating appearances in a column conditioned to values from another column

Hi everyone. I'm newbie with Power BI, so I suppose I may be asking for something trivial.

I've got a table with entrances (Date) of different users (UserID), along several months (max. one per day). Date and UserID are the only original columns. All others are calculated columns, but ID column wich is manually defined:

I want to know how many users are entering  1, 2, ... 7 days a week for every week of the year. (My idea is a matrix with Week number (1-53) for rows and  amount of days in the week (0-7) for columns ). I wasn't able to create that matrix.

Later, I created a related table with unique UserID values. I'm trying to create a columns that count days of entrance for a specific week for each UserID. But I'm not able to create it.

May someone help me? Thank you so much in advance.

Best,

Juan

2 ACCEPTED SOLUTIONS

Accepted Solutions
Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

I finally solved it in a way I don't like so much... but it works:

First, I created a new table "Entries_user_week" to calculate the amount of days every user entry to the system. For every week (1 to 53) I created manually a column (sample with week 2):

2 = calculate(count(Table1[WeekDay]);filter(Table1;Table1[UserID]='Entries_user_week'[UserID]);filter(Table1;Table1[Week]=2))

Then I created another table calculating the total amount of users with entries 1 day, 2 days,... 7 days for every week (a column per week, manually defined):

02 = calculate(count(Entries_user_week[2]);filter(Entries_user_week;Entries_user_week[2]=Users_NDays_week[Days per Week]))

I'm sure this is not the best way to get that result. I would like to know how to avoid the manual creation of 53 colums in each table.

Best,

Juan

Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

Hi again,

As I supose, it was much easier i had done it before.

The question can be solved without creating any another table o measure. It just needs some data preparing work with Query Editor:

1.- We need to duplicate Date column and Transform it to get Week number. I named it [WeekID]

2.- We need to remove duplicates because many users have several entrances per day. So I solved it creating a new column witch concatenate UserID-Date. Then we can use 'Remove Duplicates' on that column.

3.- Finally, we can Group [UserID] and [WeekID] by UserID counting rows of WeekID. This action will give us a table with three columns: [UserID], [WeekID] and [DaysPerWeek].

With this Table we can use Matrix Visualization tool: [DaysPerWeek] as Rows, [WeekID] as Columns and [UserID] (count) as values.

That's all. Much more simple than I did before.

5 REPLIES 5
Moderator

## Re: Calculating appearances in a column conditioned to values from another column

Hi @jzamoro,

I make a test using the following sample data.

You don’t need to create any extra columns to achieve your requirement. Just create a Matrix visual using Week, WeekDay and UserID as follows, then add a UserID slicer to filter the Matrix. For more details, please review this attached PBIX file.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

Thanks a lot for your help, Lydia. I think I didn`t explain myself completely right. I need to calculate how many users access n-days per week. I don't mind if a user access on Monday and Tuesday or Friday and Saturday on a specific week. Both cases will count 2 for that week & user. If we filter to UserID A003 and F005 we would have:

Week 25: 2 users access 1 day. No users access 2, 3... 7 days on that week.

Week 26: One user access 1 day. One user access 2 days. No users access 3,... 7 days on that week.

Thanks again for your help.

Best,

Juan

Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

I finally solved it in a way I don't like so much... but it works:

First, I created a new table "Entries_user_week" to calculate the amount of days every user entry to the system. For every week (1 to 53) I created manually a column (sample with week 2):

2 = calculate(count(Table1[WeekDay]);filter(Table1;Table1[UserID]='Entries_user_week'[UserID]);filter(Table1;Table1[Week]=2))

Then I created another table calculating the total amount of users with entries 1 day, 2 days,... 7 days for every week (a column per week, manually defined):

02 = calculate(count(Entries_user_week[2]);filter(Entries_user_week;Entries_user_week[2]=Users_NDays_week[Days per Week]))

I'm sure this is not the best way to get that result. I would like to know how to avoid the manual creation of 53 colums in each table.

Best,

Juan

Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

Hi @v-yuezhe-msft. Thanks again for your help. I posted a'solution' few minutes ago. I'm sure there must be another (much easier) way to get the result I was looking for... but it seems to work..

Best,

Juan

Frequent Visitor

## Re: Calculating appearances in a column conditioned to values from another column

Hi again,

As I supose, it was much easier i had done it before.

The question can be solved without creating any another table o measure. It just needs some data preparing work with Query Editor:

1.- We need to duplicate Date column and Transform it to get Week number. I named it [WeekID]

2.- We need to remove duplicates because many users have several entrances per day. So I solved it creating a new column witch concatenate UserID-Date. Then we can use 'Remove Duplicates' on that column.

3.- Finally, we can Group [UserID] and [WeekID] by UserID counting rows of WeekID. This action will give us a table with three columns: [UserID], [WeekID] and [DaysPerWeek].

With this Table we can use Matrix Visualization tool: [DaysPerWeek] as Rows, [WeekID] as Columns and [UserID] (count) as values.

That's all. Much more simple than I did before.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 60 members 1,305 guests
Recent signins: