Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
@v-mengzhu-msft 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"
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_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 |
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
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |