Help with Summarising a Table

[HiWtHi_27]

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:

Thanks in advance!

[HiWtHi_27]

Thanks so much @123abc  !!

[123abc · Accepted solution]

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.