Forum Discussion
Count based on the multiple column (DAX)
Hi,
Can you please advise regarding count based on the multiple column. I can't found any similar query in Power BI forum so I need advice on this query.
In Excel it's very simple formula but I don't know how can I archive in Power BI.
Excel formula =IF(COUNT(A2:G2)=1,"ONE",IF(COUNT(A2:G2)>1,"MULTIPLE"))
I am looking for new calculated column option.
Rule:
From Column A:G the total count is greater than 1 then the desired result is "Multiple".
From Column A:G the count is equal to 1 then the desired result is "one".
Data:
A13 A14 A15 A16 A22 A23 A26 DESIRED RESULT(IF FORMULA)
2 ONE
4 6 MULTIPLE
1 1 1 1 MULTIPLE
1 1 MULTIPLE
1 1 MULTIPLE
1 ONE
1 ONE
1 ONE
1 ONE
1 ONE
1 ONE
1 ONE
1 ONE
1 ONE
3 1 MULTIPLE
3 1 MULTIPLE
1 1 2 MULTIPLE
2 ONE
2 ONE
1 1 1 1 MULTIPLE
3 1 2 8 2 MULTIPLE
2 ONE
4 2 2 1 1 MULTIPLE
Snapshot of Desired result:
Hi Saxon10
I have used the below DAX to create a column
Desired Result = IF(IF(ISBLANK(Table1[A]),0,1) + IF(ISBLANK(Table1[B]),0,1) + IF(ISBLANK(Table1[C]),0,1) + IF(ISBLANK(Table1[D]),0,1) + IF(ISBLANK(Table1[E]),0,1) + IF(ISBLANK(Table1[F]),0,1) + IF(ISBLANK(Table1[G]),0,1) = 1, "One","Multiple")and got the below output.
4 Replies
Hi Saxon10
Try this
Column = IF ([Column1]+[Column2]+[Column3]+[Column4]+[Column5]+[Column6]+[Column7] = 1, "One","Multiple")Regards
Phil
Hi,
Thanks for your reply and sorry for the late reply.
Here I am looking for overall columns (from column 1 to 7) count equal to 1 then return is "One" and if not then return "Multiple" not individual column count. Please refer the snapshot were it will give a different result.
Hi Saxon10
I have used the below DAX to create a column
Desired Result = IF(IF(ISBLANK(Table1[A]),0,1) + IF(ISBLANK(Table1[B]),0,1) + IF(ISBLANK(Table1[C]),0,1) + IF(ISBLANK(Table1[D]),0,1) + IF(ISBLANK(Table1[E]),0,1) + IF(ISBLANK(Table1[F]),0,1) + IF(ISBLANK(Table1[G]),0,1) = 1, "One","Multiple")and got the below output.
Hi,
Thanks for your help and support. It's working well.
