IF ISBLANK Return Blank

[Saxon10]

Hi,

I have a one table, that table contain multiple columns and count. I am trying to build final status based on the columns A:G.

ITEM	A	B	C	D	E	F	G	REESULT
123	2							ONE
124	4	6						MULTIPLE
125	1	1	1	1				MULTIPLE
126	1	1						MULTIPLE
127	1	1						MULTIPLE
128	1							ONE
129	1							ONE
130	1							ONE
131	1							ONE
132	1							ONE
133	1							ONE
134	1							ONE
135	1							ONE
136	1							ONE
137	3	1						MULTIPLE
138	3	1						MULTIPLE
139	1	1	2					MULTIPLE
140	2							ONE
141	2							ONE
142	1	1	1	1				MULTIPLE
143	3	1	2	8	2			MULTIPLE
144	2							ONE
145	4	2	2	1	1			MULTIPLE
146
147
148

I am applying below mentioned DAX and it's working well but it will give a wrong result (Multiple) were columns are blanks (A:G) but desired result is blanks. Please refer 146,147 and 148.

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")

[PhilipTreacy · Accepted solution]

Hi @Saxon10 

Download sample PBIX file

Modify the DAX to this

Result = 

VAR _Count = IF(ISBLANK(Table1[A]),BLANK(),1)+IF(ISBLANK(Table1[B]),BLANK(),1)+IF(ISBLANK(Table1[C]),BLANK(),1)+IF(ISBLANK(Table1[D]),BLANK(),1)+IF(ISBLANK(Table1[E]),BLANK(),1)+IF(ISBLANK(Table1[F]),BLANK(),1)+IF(ISBLANK(Table1[G]),BLANK(),1)

RETURN IF(_Count=1, "One", IF(ISBLANK(_Count), BLANK(), "Multiple"))

Regards

Phil

[Saxon10]

Hi, 

Thank you so much for your help and sorry for the late reply. Your solution is working very well.