topic Re: Calculated column in Power BI in DAX Commands and Tips

Calculated column in Power BI

poojithas — Wed, 23 Nov 2022 16:06:00 GMT

Hi All,

I have a table as below: An account_no could have either two values Record_Set and Non-Unique/In-Complete or only have one value that is Record_Set.

Scenario 1 - Table

I need to create a new column with condition as if account_no has both the values then show as "Non-Unique" and if account_no has only one value that it should be "Unique"

Expected output

Account_no	Unique/Non-Unique (New column)
0100152603	Non-Unique
0100152131	Non-Unique
0100177639	Non-Unique
010078057	Unique

Scenario2 - Table:

I need to create a new column with condition as if account_no has both the values then show as "In-Complete" and if account_no has only one value that it should be "Complete" for each category.

This should work dynamically when the category filter is applied

Expected output

Account_no	Unique/Non-Unique (New column)
0100156233	Complete
0100156277	In-Complete
0100156398	In-Complete
0100157124	Complete
0100161293	In-complete

Could somone please let me know how to achieve the above case.

Thank you

Re: Calculated column in Power BI

Anonymous — Thu, 24 Nov 2022 06:18:28 GMT

Hi poojithas ,

Like this?

This is for your senior 1, just create one measure and a calculated column to meet your needs. As the following writes:

rows = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[Account No.]=SELECTEDVALUE('Table'[Account No.])))

new Column = IF([rows]=1,"Unique","non-unique")

For senior2, the solution process is similar to 1, just change the filter condition.

You can refer to my pbix file for better understanding.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Calculated column in Power BI

poojithas — Tue, 29 Nov 2022 06:08:06 GMT

Anonymous I have replicated the same steps but for few of the account_nos, it shows a different value.
eg:

The expected output for the highlighted account_no is complete as it has only one value"record_set"

Re: Calculated column in Power BI

Anonymous — Tue, 29 Nov 2022 09:07:09 GMT

Hi poojithas ,

What is the final desired result you want to present?

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Calculated column in Power BI

poojithas — Tue, 29 Nov 2022 10:25:42 GMT

Anonymous If there are duplicate acccount_no, i.e, if one account_no has two values for DQ_Issue (Record_set and Non-Unique) or (Record_Set or Incomplete), then the new column should have the value as "Non-Unique/Unique" or "Incomplete/Complete"

Sample data below

Table 1

Account_no	DQ_Issue
Account1	Record_Set
Account2	Record_Set
Account3	Record_Set
Account4	Record_Set
Account1	Non-Unique
Account2	Non-Unique
Account3	Non-Unique

Expected Output:

Account_no	New column
Account1	Non-Unique
Account2	Non-Unique
Account3	Non-Unique
Account4	Unique

Table 2

Account_no	DQ_Issue
Account1	Record_Set
Account2	Record_Set
Account3	Record_Set
Account4	Record_Set
Account1	Incomplete
Account2	Incomplete

Expected Output:

Account_no	New column
Account1	Incomplete
Account2	Incomplete
Account3	Complete
Account4	Complete

Table 3

Account_no	DQ_Issue
Account1	Record_Set
Account2	Record_Set
Account3	Record_Set
Account4	Record_Set
Account5	Record_Set
Account1	Record_Set
Account3	Record_Set
Account2	Incomplete
Account5	Incomplete

Expected Output:

Account_no	New column
Account1	Complete
Account2	Incomplete
Account3	Complete
Account4	Complete
Account5	Incomplete

Re: Calculated column in Power BI

Anonymous — Wed, 30 Nov 2022 09:53:50 GMT

Hi poojithas ,

As shown below are my calculations of the expected results for each of the three tables you want.

You can refer to my pbix file for better understanding.

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly