cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
poojithas
Helper I
Helper I

Calculated column in Power BI

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"

poojithas_0-1669191250930.png

 

Expected output

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

 

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

poojithas_1-1669219385276.png

 

Expected output

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

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

Thank you

1 ACCEPTED SOLUTION

Hi @poojithas ,

 

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

vmengzhumsft_0-1669801983856.png

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

 

View solution in original post

5 REPLIES 5
v-mengzhu-msft
Community Support
Community Support

Hi @poojithas ,

 

Like this?

vmengzhumsft_0-1669270191710.png

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

@v-mengzhu-msft  I have replicated the same steps but for few of the account_nos, it shows a different value.
eg:

poojithas_0-1669702043882.png

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

 

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

@v-mengzhu-msft  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_noDQ_Issue

Account1

Record_Set
Account2Record_Set
Account3Record_Set
Account4Record_Set

Account1

Non-Unique
Account2Non-Unique
Account3Non-Unique

 

Expected Output:

Account_noNew column

Account1

Non-Unique
Account2Non-Unique
Account3Non-Unique
Account4Unique

 

Table 2

Account_noDQ_Issue

Account1

Record_Set
Account2Record_Set
Account3Record_Set
Account4Record_Set

Account1

Incomplete
Account2Incomplete

 

Expected Output:

Account_noNew column

Account1

Incomplete
Account2Incomplete
Account3Complete
Account4Complete

 

Table 3

Account_noDQ_Issue

Account1

Record_Set
Account2Record_Set
Account3Record_Set
Account4Record_Set
Account5Record_Set

Account1

Record_Set
Account3Record_Set
Account2Incomplete
Account5Incomplete

Expected Output:

Account_noNew column

Account1

Complete
Account2Incomplete
Account3Complete
Account4Complete
Account5Incomplete

Hi @poojithas ,

 

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

vmengzhumsft_0-1669801983856.png

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors