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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.