Help with custom column

[Boycie92]

Hi,

I am hoping someone can help.

I have a direct query model. In Edit queries I want to be able to create a custom column based on multiple conditions.

To explain my data, I have two columns:

• UniqueID
• Category (has multiple diffrent terms)

Each UniqueID can have multiple records if they appear in multiple categories

What I need:

A custom column that will look at my UniqueID column and if the UniqueID does not have a record that either says “a” or “b”  in the category column then No else yes.

Thanks

Boycie92

[v-yuta-msft]

Hi Boycie92,

In direct query mode, you can create custom column, so you should use import mode. In import mode, you can use "custom column" instead of "conditional column" if you have multiple conditions. You can use M code below:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Category] <> "a" and [Category] <> "b" then "No" else "Yes")

 

The result is like below:

 

Regards,

Jimmy Tao

[Boycie92]

Hi @v-yuta-msft

Thanks for your reply. I can't use Import Mode as it must be direct query.

I think I have explained this wrong so let me try again.

What I want to achieve is to identify any UniqeID's that have not got an "A" or "B" in the category field.  If there is a UniqueID's that doesn’t have "A" or "B" records, then "No" else "yes"

For example

UniqueID 4 has 5 rows, each with categories that are not “A” or “B” then No

UniqueID 5 has 3 rows, 2 of them are “A” and “B”, the other is “C” then “Yes”

UniqueID 6 has 2 rows, that are “A” and “C” then “Yes”

I hope that makes it a bit clearer.

Thanks,

Boycie92

[Ashish_Mathur]

Hi,

Try this calculated column formula

=IF(COUNTROWS(FILTER(Data,Data[ID]=EARLIER(Data[ID])&&(Data[Category]="A"||Data[Category]="B")))>0,"Yes","No")

Hope this helps.

[Boycie92]

Hi @Ashish_Mathur & @v-yuta-msft

I have tried both of your suggestions, However CALCULATE, COUNTROWS and FILTER are not allowed as part of a Direct Query Model.

Do you have any other ideas?

Thanks,

Boycie92

[v-yuta-msft]

Hi Boycie92,

I'm afraid it's difficult to use custom function to achieve this. As a workaround, i would recommend you to create a calculate column using DAX as below:

Result = IF(CALCULATE(MAX(Table1[Category]), FILTER(ALLEXCEPT(Table1, Table1[UniqueID]), Table1[Category] = "a" || Table1[Category] = "b")) <> BLANK(), "Yes", "No")

 

Regards,

Jimmy Tao