Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gssarathkumar
Frequent Visitor

Column specific Exclusions in Calculated Column

I have a datatable which contains the data with below columns:

Name
Group
Caller
State
Category
Priority
Type

I also have an another table as below which I will be using as an Exclusion list.

 

NameGroupCallerStateCategoryPriorityType

AEBIDDINGSarathIncompletePM Request
AHYPERCARE    Request
AENHANCEMENT    Request
ALEVERAGED    Request
ATOOLING    Request
BDesktopKumar   Request
B Sankar   Request
BEPM & SharePoint    Request
BDMS    Request
BApplications    Request
BLinux    Request
BOperational    Request
B   IW Request
B   VA Request
B   PM Request
B   OR Request
COperational    Request
C   IW Request
C   VA Request
C   PM Request
C   OR Request
D   IW Request
D   VA Request
D   PM Request
D   OR Request
H   IW Request
H   PM Request
H   CU Request
H   RA Request
H   OR Request
H   ER Request
H   VA Request
Y   CU Request
Y   IW Request
Y   OR Request
Y   PM Request
Y   RA Request
Y   VA Request
MA   IW Request
MA   OR Request
MA   PM Request
MA   VA Request
S   CU Request
S   IW Request
S   OR Request
S   PM Request
S   RA Request
S   SH Request
S   VA Request
MS   OR Request
MS   IW Request
MS   OR Request
MS   PM Request
MS   RA Request
MS   VA Request

 

I need to match my datatable with the exclusion list and do exclude from the report.

 

For instance, Name: A with either Group or Caller or State or Category is matching with my datatable, it needs to be excluded.

 

Tried Solution:

First, I created multiple columns to extract the exclusionlist data to my datatable using below calculated column:

Category = MAXX(FILTER('Exclusionlist', Exclusionlist[Category] = datatable[Category]), Exclusionlist[Category])

 

And the I am comparing the columns using below calculated column:

CategoryMatch = IF(AND(datatable[category] <> BLANK(), SNOW_Request[SubCategory] = SNOW_Request[request_subcategory]), "Yes", "No")

 

Finally, I created the below calculated column:

Column = IF(AND(datatable[Name] = "Yes", OR(OR(datatable[Group] = "Yes", datatable[Caller] = "Yes"), datatable[Category] = "Yes")), "Exclude", "Include")

 

But I didn't get the desired output as there is a possibility of Other Names contain same Category / State / Caller.

 

I need my exclusion should happen as:

AND(Name = Yes, Group = Yes), "Exclude" or AND(Name = Yes, Caller = Yes), "Exclude" or AND(Name = Yes, Category = Yes), "Exclude" or AND(Name = Yes, Priority = Yes), "Exclude" else "Include"

 

Can anyone help me with this problem statement?

@amitchandak , @Greg_Deckler @lbendlin , @DesktopOwl @Goodlytics4U @help Ahmedx @Ahmedx @lbendlin : Please help.

1 ACCEPTED SOLUTION
v-huijiey-msft
Community Support
Community Support

Hi @gssarathkumar ,

 

Your requirement is "AND(Name = Yes, Group = Yes), "Exclude" or AND(Name = Yes, Caller = Yes), "Exclude" or AND(Name = Yes, Category = Yes), "Exclude" or AND(Name = Yes, Priority = Yes), "Exclude" else "Include""?

 

Is Yes a string or does it mean that the current column is not null? For example, "Group = Yes" means that the Group column is not null? I don't quite understand.

 

In Power Query, it is convenient to create custom columns to achieve your needs.

vhuijieymsft_0-1713508755547.png

 

If it means that the current column is not null, the syntax is as follows:

if [Name] <> Null.Type and ( [Group] <> Null.Type or [Caller] <> Null.Type or [State] <> Null.Type or [Category] <> Null.Type or [Priority] <> Null.Type) then "Exclude" else "Include"

vhuijieymsft_1-1713508755551.png

 

The same is true if it is a string, just change <> Null.Type to = "Yes".

 

The page effect is as follows:

vhuijieymsft_2-1713508791984.png

 

After saving and exiting Power Query, add the newly created custom column to the Filter and set the condition to "is not Exclude".

vhuijieymsft_3-1713508791986.png

 

The final page effect is as follows:

vhuijieymsft_5-1713508816864.png

 

pbix file is attached.

 

If you have any further questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

View solution in original post

1 REPLY 1
v-huijiey-msft
Community Support
Community Support

Hi @gssarathkumar ,

 

Your requirement is "AND(Name = Yes, Group = Yes), "Exclude" or AND(Name = Yes, Caller = Yes), "Exclude" or AND(Name = Yes, Category = Yes), "Exclude" or AND(Name = Yes, Priority = Yes), "Exclude" else "Include""?

 

Is Yes a string or does it mean that the current column is not null? For example, "Group = Yes" means that the Group column is not null? I don't quite understand.

 

In Power Query, it is convenient to create custom columns to achieve your needs.

vhuijieymsft_0-1713508755547.png

 

If it means that the current column is not null, the syntax is as follows:

if [Name] <> Null.Type and ( [Group] <> Null.Type or [Caller] <> Null.Type or [State] <> Null.Type or [Category] <> Null.Type or [Priority] <> Null.Type) then "Exclude" else "Include"

vhuijieymsft_1-1713508755551.png

 

The same is true if it is a string, just change <> Null.Type to = "Yes".

 

The page effect is as follows:

vhuijieymsft_2-1713508791984.png

 

After saving and exiting Power Query, add the newly created custom column to the Filter and set the condition to "is not Exclude".

vhuijieymsft_3-1713508791986.png

 

The final page effect is as follows:

vhuijieymsft_5-1713508816864.png

 

pbix file is attached.

 

If you have any further questions please feel free to contact me.

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors