Distinctcount and Countrows in DAX

 When to use Distinctcount and Countrows Function in DAX

Distinctcount:

Counts the number of distinct values in a column.

Expression:

Count_Of_EmpID =CALCULATE(

    DISTINCTCOUNT('Table'[EmpID]),

    ALLEXCEPT('Table', 'Table'[EmpID])

)

Explanation:

This DAX expression calculates the count of distinct employee IDs in the 'Table' table, excluding any filters on other columns. The ALLEXCEPT function removes all filters from the 'Table' table except for the EmpID column, ensuring that the count is performed on all rows in the table.

If we don't want to count EmpID whose having as 0 then follow below Expression

Count_Of_EmpID=CALCULATE(

    DISTINCTCOUNT('Table'[EmpID]),

    ALLEXCEPT('Table', 'Table'[EmpID]),

    'Table'[EmpID] <> 0

)

This expression adds an additional filter to the ALLEXCEPT function, excluding rows where the EmpID column is equal to 0. As a result, the count of distinct employee IDs will only include non-zero values.

Here's a breakdown of the expression:

* `CALCULATE`: This function allows you to evaluate an expression within a modified filter context.

* `DISTINCTCOUNT('Table'[EmpID])`: This expression counts the number of distinct values in the EmpID column of the 'Table' table.

* `ALLEXCEPT('Table', 'Table'[EmpID])`: This function removes all filters from the 'Table' table except for the EmpID column.

* `'Table'[EmpID] <> 0`: This filter excludes rows where the EmpID column is equal to 0.

By combining these elements, the expression calculates the count of distinct employee IDs, excluding any filters on other columns and excluding rows where the EmpID column is equal to 0.

Countrows:

The COUNTROWS function counts the number of rows in the specified table, or in a table defined by an expression.

Count _rows = COUNTROWS(Sales)

Explanation:

* The COUNTROWS function counts the number of rows in a table or a specified range of cells.

* Sales: This refers to the 'Sales' table in your Power BI model.

* The result of this expression will be a scalar value, which is the total number of rows in the 'Sales' table.

Example 2:

To count the number of rows in the 'Sales' table where the 'Product' column is equal to "Product A", you can use the following expression:

Count_rows_ex2 = COUNTROWS(FILTER(Sales, Sales[Product] = "Product A"))

Explanation:

* The FILTER function filters the 'Sales' table based on a specified condition. In this case, it filters the table to include only rows where the 'Product' column is equal to "Product A".

* The COUNTROWS function then counts the number of rows in the filtered table.

* The result of this expression will be a scalar value, which is the number of rows in the 'Sales' table where the 'Product' column is equal to "Product A".

Comments

Post a Comment

Popular posts from this blog

Create Calendar Table in Power BI using DAX Function

 Calendar Table using Dax Syntax: CalendarTable = ADDCOLUMNS ( CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)), "Year", YEAR([Date]), "MonthNumber", MONTH([Date]), "MonthName", FORMAT([Date], "MMMM"), "Quarter", QUARTER([Date]), "DayOfWeek", WEEKDAY([Date]), "DayOfMonth", DAY([Date]), "WeekdayName", FORMAT([Date], "dddd"), "IsWeekend", IF( OR(WEEKDAY([Date]) = 1, WEEKDAY([Date]) = 7), TRUE(), FALSE()), "IsHoliday", IF( OR( MONTH([Date]) = 1 && DAY([Date]) = 1, MONTH([Date]) = 7 && DAY([Date]) = 4), TRUE(), FALSE()) ) Explanation: CALENDAR (DATE(2024, 1, 1), DATE(2024, 12, 31)) Creates a table with a single column containing dates from January 1, 2024, to December 31, 2024. ADDCOLUMNS ( Adds new columns to the table created by the CALENDAR function. "Year", YEAR([Date]), Adds a column named "Year" that...
Read more

Calculate Sales for Last Year Month to date

Sameperiodlastyear till to date based on selection in year and month selection Problem:  If the current year and month are selected, sales data from the same period of the previous year up to the current date is needed. For instance, if it's March 2024, you'd require sales data from March 2023 up to today's date. If the selection is for a different year and month, such as January 2023, you need the entire sales data for January 2022. Solution:  For this scenario you need to create Three Dax Measures Measure 1 = (This measure is to calculate current month sales till the date) CALCULATE( SUM(CST[Total Financed Amount]), SAMEPERIODLASTYEAR( DATESYTD('Calendar Table'[Date]) ), FILTER( ALL('Calendar Table'), 'Calendar Table'[MonthNum] = MONTH(NOW()) && DAY('Calendar Table'[Date]) <= DAY(NOW()) ) ) Explanation: CALCULATE : This is a DAX function used to modify or filter the context for a calculation. It can apply additional filters to a ...
Read more

CONTAINSTRING DAX

Exploring the CONTAINSSTRING Function in DAX Understanding the CONTAINSSTRING Function At its core, the CONTAINSSTRING function enables users to determine whether a specific substring exists within a given text string. This function returns a Boolean value, TRUE if the substring is found, and FALSE if it is not. Here's the basic syntax of the CONTAINSSTRING function: CONTAINSSTRING(<text>, <substring>) <text>: The text string within which you want to search. <substring>: The substring you're searching for within the text. Practical Example: Using CONTAINSSTRING in DAX Let's delve into a practical example to grasp how the CONTAINSSTRING function works within the context of DAX. Suppose we have a dataset containing product information, including a column named ProductName. We're interested in identifying which product names contain the word "apple". We can achieve this by creating a calculated column using the CONTAINSSTRING functi...
Read more