Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jzamoro
Regular 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:

 

table_sample.jpg

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
jzamoro
Regular Visitor

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))

 

accesos por usuario-semana.jpg

 

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]))

 

total usuarios acumulados por semana.jpg

 

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

View solution in original post

jzamoro
Regular Visitor

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.

View solution in original post

5 REPLIES 5
jzamoro
Regular Visitor

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.

jzamoro
Regular Visitor

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))

 

accesos por usuario-semana.jpg

 

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]))

 

total usuarios acumulados por semana.jpg

 

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

v-yuezhe-msft
Employee
Employee

Hi @jzamoro,

I make a test using the following sample data.
1.png

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.
2.PNG3.PNG



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.

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.