Forum Discussion
Group Column with Conditions
Hi Community,
Is there a way I can achieve as shown in the pie chart. The data is replicated in the table below( all data is present in one table )
The pie chart gives distinctcount of computers with users term "Full " or "Missing", with further condition , IF "Missing" , then group into range of missing as per count of users.
a) 1-2 Missing
b)3-4 Missing
c) > 5 Missing
Answer Expected :
Computer 1 is Full
Computer 2 is in range of 1-2 Missing
Computer 3 is in range of 3-4 Missing
| Name | User | Signal | Term |
| Computer 1 | user 1 | approved | full |
| user 2 | approved | full | |
| user 3 | approved | full | |
| Computer 2 | user 4 | approved | missing |
| user 5 | Not Approved | Missing | |
| user 6 | Not Approved | full | |
| computer 3 | user 7 | approved | missing |
| user 8 | approved | missing | |
| user 9 | approved | missing |
Thanks in advance 🙂
Hi schoden ，
You can follow these steps：
Step 1,import data you provide:
Step 2,use the following dax to create a new table:
Table 2 = SUMMARIZE('Table','Table'[Name],"missing value",CALCULATE( count('Table'[Term]),FILTER('Table','Table'[Term]="missing")))
Step 3，create new column in the new table :
about =
IF ( 'Table 2'[missing value] = blank(), "full",
IF ( 'Table 2'[missing value] >=1 &&'Table 2'[missing value] <=2, "1-2 Missing",
IF ( 'Table 2'[missing value] >=3 &&'Table 2'[missing value] <=4, "3-4 Missing",
IF ( 'Table 2'[missing value] >=5 , ">5 Missing"
) ) ) )
about2 = IF ( 'Table 2'[about] ="full", "full","missing")
Step 4，try the following measure:
About4 =
VAR about3 =
CALCULATE (
COUNT('Table 2'[about]),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[missing value]<>BLANK()&&'Table 2'[about]=MAX('Table 2'[about]))
)
RETURN
( about3 )
Step 5,create visual base on the new table , Finally you will see:
If my answer helps you, you can mark it as an answer, thank you!
Best Regards,
Lucien
5 Replies
schoden check this dynamic basket analysis and that is what you need
Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi schoden ，
You can follow these steps：
Step 1,import data you provide:
Step 2,use the following dax to create a new table:
Table 2 = SUMMARIZE('Table','Table'[Name],"missing value",CALCULATE( count('Table'[Term]),FILTER('Table','Table'[Term]="missing")))
Step 3，create new column in the new table :
about =
IF ( 'Table 2'[missing value] = blank(), "full",
IF ( 'Table 2'[missing value] >=1 &&'Table 2'[missing value] <=2, "1-2 Missing",
IF ( 'Table 2'[missing value] >=3 &&'Table 2'[missing value] <=4, "3-4 Missing",
IF ( 'Table 2'[missing value] >=5 , ">5 Missing"
) ) ) )
about2 = IF ( 'Table 2'[about] ="full", "full","missing")
Step 4，try the following measure:
About4 =
VAR about3 =
CALCULATE (
COUNT('Table 2'[about]),
FILTER (
ALL ( 'Table 2' ),
'Table 2'[missing value]<>BLANK()&&'Table 2'[about]=MAX('Table 2'[about]))
)
RETURN
( about3 )
Step 5,create visual base on the new table , Finally you will see:
If my answer helps you, you can mark it as an answer, thank you!
Best Regards,
Lucien
v-luwang-msft Thank you heaps ...Solved My problem 😄
