overwrite visual filters in calculated column

[han_rj]

Hi All, Need help overwrite visual filters on the same feild but only for a single row in a table.
I have a calculated column as folows

product_category_new = IF('Product Subcategory'[Subcategory] in {"Bluetooth Headphones","MP4&MP3"},"Audio",

if('Product Subcategory'[Subcategory] in {"Camcorders","Digital Cameras"}, "Camera",IF('Product Subcategory'[Subcategory] IN {"Desktop","Laptops","Printers, Scanners & Fax"}, "Computers")))

I need to overwrite the visual filters for a single row where Product Category is Computer alone.

The current view has pre filters like CountryRegion = France or Canada and occupation = Management how to override the filter for row computer with CountryRegion canada and occupation Clerk

Attached is the existing view and the expected view to build, Please may I have help

[v-lgarikapat · Accepted solution]

Hi @han_rj ,

Thanks for reaching out to the Microsoft fabric community forum.

@SamsonTruong , @danextian , 

Thanks for your prompt response

@han_rj ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Best Regards,

Lakshmi Narayana

[v-lgarikapat]

Hi @han_rj ,

If your issue has been resolved, please consider marking the most helpful reply as the accepted solution. This helps other community members who may encounter the same issue to find answers more efficiently.

If you're still facing challenges, feel free to let us know we’ll be glad to assist you further.

Looking forward to your response.

Best regards,
LakshmiNarayana.

[v-lgarikapat]

Hi @han_rj ,

If your question has been answered, kindly mark the appropriate response as the Accepted Solution. This small step goes a long way in helping others with similar issues.

We appreciate your collaboration and support!

Best regards,
LakshmiNarayana

[v-lgarikapat]

Hi @han_rj ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the same issue, we kindly request you to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If our response was helpful, please mark it as Accepted as Solution and consider giving a Kudos. Feel free to reach out if you need any further assistance.

Best Regards,

Lakshmi Narayana

[danextian]

When you say computers alone, do you mean to apply the filter for that category alone ane everythign else remains  the same?  Would have been easier for us to understand if you gave us a sample data and from the same sample data, your expected result.

VAR _computersOnly =
    CALCULATE (
        SUM ( tbl[value] ),
        KEEPFILTERS ( 'Product'[category] = "computers" ),
        KEEPFILTERS ( Countryregion[countryregion] = "Canada" ),
        KEEPFILTERS ( occupation[occupation] = "clerk" )
    )
VAR _other =
    CALCULATE (
        SUM ( tbl[value] ),
        KEEPFILTERS ( 'Product'[category] <> "computers" )
    )
RETURN
    _computersOnly + _other

Note: if filters are from the same table, there's no need to use multiple KEEPFILTERS for that table. Example: 

 KEEPFILTERS ( 'table'[category] = "computers"  &&  'table'[countryregion] = "Canada" )

[SamsonTruong]

Hi @han_rj ,

To have the sales amount remove the visual filters when the product category is "Computer", you can leverage the following DAX measure for sales amount:

SalesAmount_OverrideComputers =
VAR IsComputers = SELECTEDVALUE('Product Subcategory'[product_category_new]) = "Computers"
RETURN
IF(
    IsComputers,
    CALCULATE(
        SUM('Sales'[SalesAmount]),
        REMOVEFILTERS('Product Subcategory')
    ),
    SUM('Sales'[SalesAmount])
)

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[han_rj]

Hi @SamsonTruong thank you for responding , I have to override the above filters like country and occupation on the row Computer

[SamsonTruong]

Hi @han_rj ,

Which column are you trying to override the visual filters for. Are you trying to override the filters on the Sales Amount column or the calculated column you created for product_category_new. 

If you are trying to have the sales amount for computer override the visual filters, creating the DAX measure in the previous response and using that measure to replace your Sales Amount will ensure that Sales Amount for Computer will not have the visual filters applied.

[han_rj]

I need to overwrite the visual filters for a single row where Product Category is Computer .

The current view has pre filters like CountryRegion = France or Canada and occupation = Management how to override the filter for row computer with CountryRegion canada and occupation Clerk

[SamsonTruong]

Hi @han_rj ,

To achieve your desired result, please follow the steps below:

Create a new measure using the following DAX:

SalesAmount_OverrideComputers =
VAR IsComputers = SELECTEDVALUE('Product Subcategory'[product_category_new]) = "Computers"
RETURN
IF(
    IsComputers,
    CALCULATE(
        SUM('Sales'[SalesAmount]),
        ALL('Product Subcategory'[product_category_new]),    
        'Customer'[CountryRegion] = "Canada",                
        'Customer'[Occupation] = "Clerk"
    ),
    SUM('Sales'[SalesAmount])  
)

Once created, remove your current Sales Amount column from your visual. Then add the newly created measure. This will make sure that the visual filters still apply to your other rows. This measure will remove the visual filters when calculating the Sales Amount for the Computer category. When adding the new measure into your visual, the Sales Amount for your other categories will remain the same.

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,

Samson

[han_rj]

But for computer I need to remove CountryRegion = France or Canada and occupation = Management and override the filter with CountryRegion canada and occupation Clerk. This Part is not there. 

More precisely my question is how to overwrite filters on a single row alone for visuals filters on the same column

[SamsonTruong]

Hi @han_rj ,

Apologies, I missed the portion of overriding with new filters, please try the same steps with the adjusted DAX measure:

SalesAmount_OverrideComputers =
VAR IsComputers = SELECTEDVALUE('Product Subcategory'[product_category_new]) = "Computers"
RETURN
IF(
    IsComputers,
    CALCULATE(
        SUM('Sales'[SalesAmount]),
        ALL('Product Subcategory'[product_category_new]), 
        'Customer'[CountryRegion] = "Canada",                   
        'Customer'[Occupation] = "Clerk"
    ),
    SUM('Sales'[SalesAmount])  
)

This will first remove the filters on the Computers category, then apply the new filters.

[han_rj]

No this does not gives the correct value, 4329 should have been the sales in computer for the measure SalesAmount_OverrideComputers but there is someother value

[SamsonTruong]

@han_rj Are there any slicers in your report? The current DAX will ignore all filters on the report including slicers, which may be causing the discrepancy. The one below will respect slicers and only override the visual level filters for CountryRegion and Occuptation:

SalesAmount_OverrideComputers =
VAR IsComputers = SELECTEDVALUE('Product Subcategory'[product_category_new]) = "Computers"
RETURN
IF(
    IsComputers,
    CALCULATE(
        SUM('Sales'[SalesAmount]),
        ALL('Product Subcategory'[product_category_new]),  
        REMOVEFILTERS('Customer'[CountryRegion]),          
        REMOVEFILTERS('Customer'[Occupation]),
        'Customer'[CountryRegion] = "Canada",            
        'Customer'[Occupation] = "Clerk"
    ),
    SUM('Sales'[SalesAmount])
)

[han_rj]

This is removing country and occupation entirely on Computers