topic Re: Column Blank and Not Blank in DAX Commands and Tips

Column Blank and Not Blank

Tob_P — Thu, 27 Oct 2022 08:44:24 GMT

Hi,

I'm looking to create a customer column based on two columns in the same table.

If Column 1 contains a number (3 digits) and Column 2 is contains the same number , then I want to label it 'Direct'. If the digits in either column do not match, then label it 'Indirect'. The caveat is that on occassion, there may be data in column 1, but no data in column 2.

Any help would be appreciated!

Re: Column Blank and Not Blank

Anonymous — Thu, 27 Oct 2022 09:22:39 GMT

TEST =

if(ISBLANK(Feuil6[col2]),"You did not say what uou whant to put :)",

if(Feuil6[col1]=Feuil6[col2],"Direct","Indirect")

)

Re: Column Blank and Not Blank

Tob_P — Thu, 27 Oct 2022 09:44:58 GMT

Thanks Anonymous

Apologies - I did not explain this well at all.

Col 1	Col 2	Output
222	222	Direct
222	225	Indirect
222	227	Indirect
222		Direct
227	222	Indirect
	222	Indirect
222	222	Direct

Working on the basis of code 222...

if columns 1 & 2 match, then direct
if column 1 has code 222 and a different code in column 2, then indirect
if column 1 has code 222 and column 2 is blank, then direct
if column 2 has no code and column 2 is populated, then indirect.

Re: Column Blank and Not Blank

Anonymous — Thu, 27 Oct 2022 09:59:05 GMT

Ok. no probs. Try this :

TEST =

if(and(ISBLANK(Feuil6[col2]), not(ISBLANK(Feuil6[col1]))),"Direct",

if(and(ISBLANK(Feuil6[col1]), not(ISBLANK(Feuil6[col2]))),"Indirect",

if(Feuil6[col1]=Feuil6[col2],"Direct","Indirect")

)

Re: Column Blank and Not Blank

tamerj1 — Thu, 27 Oct 2022 11:22:54 GMT

Hi Tob_P
Please refer to attached file with the solution

Output2 = IF ( 'Table'[Col 1] IN ALLNOBLANKROW ( 'Table'[Col 2] ), IF ( 'Table'[Col 1] = 'Table'[Col 2] || 'Table'[Col 2] = BLANK ( ), "Direct", "Indirect" ) )