Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Sample data from the 3 tables:
Thanks in advance!
Solved! Go to 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.
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.
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.
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.
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |