Count number of projects

[anusha_2023]

I have a Project Table and a Resource tables related through many to one Booking fact table. Data model as below.

I need to have a matrix visual as below but insted of Resouce names list I need count of resources and total booked hours  as values in the matrix.

This is how I selected the fields from table and values and I sttedped out the Resource Names from the Table and hide +/i as well.

Please let me know the possibilities of getting the count of Resources insted of list of names. Thank you.

[Anonymous]

Hi @anusha_2023 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[123abc]

It seems like you're using Power BI to create a matrix visual and want to display the count of resources and total booked hours instead of listing the resource names. Here are the steps you can follow to achieve this:

1. Create a New Measure for Resource Count:

   • In Power BI Desktop, go to the "Model" view.

   • Create a new measure by clicking on "New Measure" and use the following DAX formula:

ResourceCount = COUNTROWS(ResourceTable)

1. • Replace "ResourceTable" with the actual name of your resource table.

2. Modify Matrix Visual:

   • Go back to the "Report" view.
   • Drag and drop the "Project Name" from your Project Table to Rows.
   • Drag and drop the newly created "ResourceCount" measure to the Values area.

3. Format Total Booked Hours:

   • Drag and drop the "Total Booked Hours" field to the Values area as well.

4. Hide Resource Names:

   • In the Values area, remove the "Resource Names" field.

5. Format as Matrix:

   • Make sure your visual is formatted as a matrix.

Now, your matrix visual should display the count of resources and total booked hours for each project. The "Resource Names" won't be listed individually, and instead, you'll see the count.

Please note that the steps might vary slightly based on your specific data model and field names, so adjust accordingly. Additionally, ensure that your relationships between tables are set up correctly for accurate results.

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.

[anusha_2023]

As resource and Project are two dimentins and if set countrows measure on councting resource and resourcegroups its working. But if I place Project name and count of resources as measure on values then I am getting same count for all proects. This is the first issue. 

Secondly on columns section I need year_month column form Date table. So after that both measures bookinghours and getcountrows are repeating for all columns of each month. I need only BookedHours should come as values for each month and count of Resource measure should be as a separate row only once. Please check the below screen shot.

[123abc]

It seems like you are facing a couple of issues: the count of resources being the same for all projects and the repetition of measures for each month in the matrix. Let's address each issue:

Issue 1: Count of Resources for All Projects

The issue of getting the same count for all projects may arise if you are not properly considering the relationship between the Project and Resource tables. Make sure that you have a relationship established between the Project table and the Resource table. In the Power BI Desktop, you can create relationships in the "Model" view.

Ensure that you have a proper relationship between the Project table and the Resource table, and then use the correct fields when creating the count measure.

Issue 2: Repetition of Measures for Each Month

If you're facing issues with the repetition of measures for each month, you need to ensure that you are using the Year_Month column from the Date table correctly. Follow these steps:

1. Create a Year_Month Column in the Date Table:

   • If you haven't already, create a Year_Month column in your Date table. You can use a DAX formula like this:

Year_Month = FORMAT('Date'[Date], "YYYY-MM")

1. Adjust Matrix Visual:

   • In the matrix visual, ensure that you are using the Year_Month field from the Date table in the Columns area.
   • Drag the Project field to the Rows area.
   • Drag the "ResourceCount" measure to the Values area.
   • Drag the "BookedHours" measure to the Values area.

2. Adjust Aggregation for BookedHours:

   • Click on the dropdown arrow next to the "BookedHours" measure in the Values area.
   • Choose the appropriate aggregation method (e.g., Sum) to ensure that it doesn't repeat for each row.

By following these steps, you should be able to have a matrix visual with the count of resources as a separate row for each project and the booked hours displayed only once for each month. Ensure that your relationships between tables are correctly set up, and use the appropriate fields in the matrix visual.

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.