Скачать или смотреть Counting Occurrences of Values in Power BI: The Ultimate Guide

Learn how to efficiently count occurrences of project start and end dates in Power BI using DAX, and create a well-structured report that summarizes your data effectively.
---
This video is based on the question https://stackoverflow.com/q/68588097/ asked by the user 'Sam.H' ( https://stackoverflow.com/u/10299633/ ) and on the answer https://stackoverflow.com/a/68588557/ provided by the user 'Wouter' ( https://stackoverflow.com/u/13636376/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Count Occurances of value in 2 columns

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Count Occurrences of Values in Power BI

If you’re working with Power BI and you find yourself needing to analyze the start and end dates of projects, you’re not alone. Many users encounter challenges when attempting to create reports that summarize this kind of time-based data. In this post, we’ll break down a common problem: how to create a smaller table that counts the number of projects that started and ended during each year and quarter.

The Challenge: Summarizing Your Project Data

You may have a table that looks like this:

Project NumberStartedEndedxxxx232019-012019-03xxxx242019-032020-01xxxx252019-032020-02What you want is a summary table that counts how many projects started and ended during each Year & Quarter, like below:

Year & QuarterProject StartedProject Ended2019-01102019-03212019-0400The Solution: Using DAX to Create a New Table

To achieve this, we can utilize DAX (Data Analysis Expressions) commands in Power BI. Specifically, we’ll use the UNION function along with SELECTCOLUMNS to create a new table that unifies the data from both the starting and ending columns.

Step 1: Create the New Table

Here’s how you can set up your DAX formula:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Formula

UNION: This function combines the results of two tables into one.

SELECTCOLUMNS: This function allows you to select specific columns from the original table and manipulate them within the new table.

Inside each SELECTCOLUMNS, we define:

"date": The date from either the started or ended column.

"project": The project number, which remains unchanged.

"Started": Set to 1 when a project starts and 0 when it ends.

"Ended": Set to 1 when a project ends and 0 when it starts.

Step 2: Creating the Summary Table

Once the new table (let's call it NewTable) is created, you can pivot this data to count how many projects started and ended for each unique date.

Load the NewTable into Power BI.

Create a Pivot Table where you use date as rows and sum up the Started and Ended columns to get the counts.

Final Thoughts

Creating summary reports in Power BI using DAX doesn’t have to be daunting. With the right formula, like the one we explored, you can quickly count occurrences of project start and end dates in a well-structured table. This way, you'll be able to derive meaningful insights from your data with ease.

If you have any questions or run into issues applying this solution, feel free to ask for help!