Create a custom column for a combination of conditions

[Anonymous]

Hi All,

I have the below table and i would like a add a "new column" which gives me the value "BINGO!" if any of the rows for that customer has "Attribute 2" Yes and none of the rows for that customer have"Attribute 1" as Yes.

Customer	Product	Attribute 1	Attribute 2	New column
A	1	No	No
A	2	No	Yes	BINGO!
A	3	No	No
B	4	Yes	No
B	1	No	No

The final requirement is to show this kind of distribution on a chart. How many Customers have value BINGO!.

Any suggestions?

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Yuna

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks for replying. I have modified the function.

column = 

var x1=SUMMARIZE(FILTER(ALL('New_test'),[Attribute 1]="Yes"),[Customer])

return

IF([Customer] in x1,BLANK(),IF([Attribute 2]="Yes","BINGO!",BLANK()))

Here is my test table and test result.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous ,

Based on your description, you can create a calculated column as follows.

Test = 

var x1=SUMMARIZE(FILTER(ALL('Table'),'Table'[Attribute 1]="Yes"),'Table'[Customer])

return

IF([Customer]<>x1&&[Attribute 2]="Yes","BINGO!",BLANK())

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Yuna,

Thanks for you time. 

When applying your solution I get below message:

"A table of multiple values was supplied where a single value was expected."

It seems X1 is being passed more than one rows as Attribute 1 in my data is set to Yes more than once.

The solution suggested by you has considered the sample data shared by me which is a subset of the entire dataset. You seem to have assumed that Attribute 1 will have Yes only once in the whole table but this is not the case in the real dataset.

If you think I have misunderstood or have a better idea please let me know.

[Ashish_Mathur]

Hi,

The first row of Customer A has a Yes appearing under attribute 1.  Since this violated your condition, there should not be a BINGO appearing in any row for the Customer.  Please clarify.

[Anonymous]

Hi Ashish,

Thanks for looking into my query. I made a mistake. Please consider below sample data for analysis.

Customer	Product	Attribute 1	Attribute 2	New column
A	1	No	No
A	2	No	Yes	BINGO!
A	3	No	No
B	4	Yes	No
B	1	No	No

[Ashish_Mathur]

Hi,

Try this calculated column formula

=if(Data[Attribute 2]="Yes",if(AND(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])&&Data[Attribute 1]="No"))=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer]=EARLIER(Data[Customer]))),CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Customer]=EARLIER(Data[Customer])&&Data[Attribute 2]="Yes"))>=1),"Bingo",BLANK()),BLANK())

Hope this helps.

[Anonymous]

Hi,

I get a syntax error at the first line. 

[Ashish_Mathur]

Hi,

My formula is a DAX formula which has to be written as a calculated column formula in the Table.  You are trying to write the formula in the Query Editor.  Go to the Data tab at the extreme left hand side panel and select the table.  Click on Add column in the ribbon and write the formula there.