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

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

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

 

HiWtHi_27_0-1709002551120.png

HiWtHi_27_1-1709002690642.png

Sample data from the 3 tables:

HiWtHi_27_2-1709002777971.png

Thanks in advance!

1 ACCEPTED SOLUTION
123abc
Community Champion
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.

View solution in original post

2 REPLIES 2
HiWtHi_27
New Member

Thanks so much @123abc  !!

123abc
Community Champion
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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors