Forum Discussion
IF Statement with Multiple Columns/Criteria
Hi,
I have the following sample data:
| Ref no. | Part No. 1 | Part No. 2 | Part Name | Issue | Status | Owner | Closed Date |
| X1 | B1 | Part 2 | |||||
| X1 | A2 | B2 | Part 1 | Y | Open | Person A | |
| X2 | Part 3 | Y | Closed | Person B | 10/06/20 | ||
| X3 | A4 | Part 4 | Y | Closed | Person B | 06/06/20 | |
| X2 | A5 | Part 5 | |||||
| X5 | A6 | B6 | |||||
| X4 | A7 | B7 | Part 7 | Y | Open | Person C |
I need to calculate how many completed rows there are using the following:
- If column Issue is blank then it needs to count how many distinct rows (using column Ref No.) there are as well as ensuring column Part Name is not blank and either Part No. 1 or Part No. 2 is also not blank. For the above sample data the answer will be 2.
- If column Issue is Y then it needs to count how many distinct rows (using column Ref No.) there are as well as ensuring columns Part Name, Owner, Closed Date are not blank and either Part No. 1 or Part No. 2 is also not blank, and column Status is Closed. For the above sample data the answer will be 1.
- The measure (or measures) will calculate the above total and produce a grand total of 3 (adding the total of items 1 and 2).
I hope the above makes sense. Any help is greatly appreciated.
Hi PowerBI-Newbie ,
Check the following measures.
Measure1 = CALCULATE(DISTINCTCOUNT('Table'[Ref no.]),FILTER('Table','Table'[Issue]=BLANK()&&'Table'[Part Name]<>BLANK()&&('Table'[Part No. 1]<>BLANK()||'Table'[Part No. 2]<>BLANK()))) Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Ref no.]),FILTER('Table','Table'[Issue]="Y"&&('Table'[Part No. 1]<>BLANK()||'Table'[Part No. 2]<>BLANK())&&'Table'[Part Name]<>BLANK()&&'Table'[Owner]<>BLANK()&&'Table'[Closed Date]<>BLANK()&&'Table'[Status]="Closed")) Measure3 = SUMX('Table',[Measure1]+[Measure2])Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi PowerBI-Newbie ,
Check the following measures.
Measure1 = CALCULATE(DISTINCTCOUNT('Table'[Ref no.]),FILTER('Table','Table'[Issue]=BLANK()&&'Table'[Part Name]<>BLANK()&&('Table'[Part No. 1]<>BLANK()||'Table'[Part No. 2]<>BLANK()))) Measure2 = CALCULATE(DISTINCTCOUNT('Table'[Ref no.]),FILTER('Table','Table'[Issue]="Y"&&('Table'[Part No. 1]<>BLANK()||'Table'[Part No. 2]<>BLANK())&&'Table'[Part Name]<>BLANK()&&'Table'[Owner]<>BLANK()&&'Table'[Closed Date]<>BLANK()&&'Table'[Status]="Closed")) Measure3 = SUMX('Table',[Measure1]+[Measure2])Best Regards,
Jay
Community Support Team _ Jay Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Anonymous , I had to tweak it slightly but it worked.
Hi PowerBI-Newbie ,
Could you please show me the desired result based on the sample data? Excel or screenshot is fine.
Thanks.
Aiolos Zhao
