Dax calculation Filling Null based on ID column

Dax calculation Filling Null based on ID column 

Real Time Problem:

I have a table that has two main columns: Account ID and Customer Name. The Customer Name column contains a lot of blanks but the Account ID field is complete, so I was wondering if there's a way to get PowerBI to recognise that Account ID 1 is used against the Customer Name, and therefore to fill in any blanks as a result of this? I know this is really confusing, here's an example:

 Name    AccountID

John        1
John        1
Null        1
John        1


So is there a way to get PowerBI to recognise that EmpName = Account ID 1, therefore any rows that have the Account ID 1 should fill any blank cells in the Customer Name column with EmpName?

 

I want these null cells filled so I can use the Customer Name as a Slicer.

Solution:


Filled_Name = 

IF(

    ISBLANK('YourTableName'[Name]),

    CALCULATE(

        FIRSTNONBLANK('YourTableName'[Name], 1),

        FILTER(

            'YourTableName',

            'YourTableName'[Account_ID] = EARLIER('YourTableName'[Account_ID]) &&

            NOT(ISBLANK('YourTableName'[Name]))

        )

    ),

    'YourTableName'[Name]

)

Explanation:


  • Filled_Name: This is the name of the calculated column or measure you're creating. It represents the column that will contain the filled names.


  • IF(ISBLANK('YourTableName'[Name]), ...): This is the start of an IF statement. It checks if the Name column is blank for the current row.


  • CALCULATE(FIRSTNONBLANK('YourTableName'[Name], 1), ...): If the Name column is blank, this part of the expression calculates the first non-blank Name for the same Account_ID. It uses the CALCULATE function to evaluate an expression in a modified filter context.


  • FILTER('YourTableName', ...): This function filters the table to rows where the conditions inside the filter function are met.


  • 'YourTableName'[Account_ID] = EARLIER('YourTableName'[Account_ID]) && NOT(ISBLANK('YourTableName'[Name])): This condition filters the table to rows where the Account_ID matches the current row's Account_ID and the Name is not blank.


  • 'YourTableName'[Name]: This is the column being filtered.


  • 'YourTableName'[Name]: If the Name column is not blank, this part of the expression simply returns the original Name.

Explanation Summary:
This DAX expression creates a calculated column in Power BI called Filled_Name. It checks if the Name column is blank for each row. If it is blank, it looks for the first non-blank Name associated with the same Account_ID. If a non-blank Name is found, it fills in the blank; otherwise, it keeps the original Name.

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