Count based on the multiple column (DAX)

[Saxon10]

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:

[Anonymous · Accepted solution]

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.

[Saxon10]

Hi,

Thanks for your help and support. It's working well. 

[PhilipTreacy]

Hi @Saxon10 

Download sample PBIX file

Try this

Column = IF ([Column1]+[Column2]+[Column3]+[Column4]+[Column5]+[Column6]+[Column7] = 1, "One","Multiple")

Regards

Phil

[Saxon10]

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.