cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## Help with Summarising a Table

Hi,

I'm new in using Power BI and DAX and I'm struggling with the understanding the use of Summarize, DistinctCount and RelatedTable. Hoping to get some help and learn from anyone here please. I'm trying to get the table visual and score cards below to show:

1. Total no. of companies that have undergone some maintenance (i.e. 3)
2. Total no. of accumulated MaintenanceID for those 3 companies (i.e. 29)

Sample data from the 3 tables:

1 ACCEPTED SOLUTION
Community Champion

To achieve the desired results in Power BI using DAX, you can follow these steps:

Assuming you have two tables: one containing company information and another containing maintenance records with a relationship established between them based on a common key (e.g., CompanyID), you can create calculated measures to display the total number of companies that have undergone maintenance and the total number of accumulated MaintenanceID for those companies.

1. Total number of companies that have undergone maintenance: You can use the DISTINCTCOUNT function to count the distinct number of companies that have undergone maintenance. Assuming you have a column named MaintenanceID in your maintenance table, you can create a measure like this:

TotalCompaniesWithMaintenance =
CALCULATE(
DISTINCTCOUNT('Maintenance'[CompanyID]),
FILTER('Maintenance', 'Maintenance'[MaintenanceID] <> BLANK())
)

Total number of accumulated MaintenanceID for those companies: You can use the SUMX function to iterate through each company that has undergone maintenance and sum up the MaintenanceID. Here's how you can create the measure:

AccumulatedMaintenanceIDs =
SUMX(
VALUES('Maintenance'[CompanyID]),
CALCULATE(
COUNTROWS('Maintenance'),
'Maintenance'[MaintenanceID] <> BLANK()
)
)

Once you have created these measures, you can add them to your table visual and score cards to display the desired information. Make sure to replace 'Maintenance' with the actual name of your maintenance table and adjust column names accordingly if they differ from the examples provided.

These measures will calculate the total number of companies that have undergone maintenance and the total number of accumulated MaintenanceID for those companies based on the data in your Power BI model.

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.

2 REPLIES 2
New Member

Thanks so much @123abc  !!

Community Champion

To achieve the desired results in Power BI using DAX, you can follow these steps:

Assuming you have two tables: one containing company information and another containing maintenance records with a relationship established between them based on a common key (e.g., CompanyID), you can create calculated measures to display the total number of companies that have undergone maintenance and the total number of accumulated MaintenanceID for those companies.

1. Total number of companies that have undergone maintenance: You can use the DISTINCTCOUNT function to count the distinct number of companies that have undergone maintenance. Assuming you have a column named MaintenanceID in your maintenance table, you can create a measure like this:

TotalCompaniesWithMaintenance =
CALCULATE(
DISTINCTCOUNT('Maintenance'[CompanyID]),
FILTER('Maintenance', 'Maintenance'[MaintenanceID] <> BLANK())
)

Total number of accumulated MaintenanceID for those companies: You can use the SUMX function to iterate through each company that has undergone maintenance and sum up the MaintenanceID. Here's how you can create the measure:

AccumulatedMaintenanceIDs =
SUMX(
VALUES('Maintenance'[CompanyID]),
CALCULATE(
COUNTROWS('Maintenance'),
'Maintenance'[MaintenanceID] <> BLANK()
)
)

Once you have created these measures, you can add them to your table visual and score cards to display the desired information. Make sure to replace 'Maintenance' with the actual name of your maintenance table and adjust column names accordingly if they differ from the examples provided.

These measures will calculate the total number of companies that have undergone maintenance and the total number of accumulated MaintenanceID for those companies based on the data in your Power BI model.

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors