Forum Discussion

han_rj's avatar
han_rj
Icon for Helper IV rankHelper IV
1 year ago
Solved

overwrite visual filters in calculated column

  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'[Subcatego...
Need Help
  • v-lgarikapat's avatar
    v-lgarikapat
    1 year ago

    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

SamsonTruong's avatar
SamsonTruong
Icon for Super User rankSuper User
1 year ago

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's avatar
han_rj
Icon for Helper IV rankHelper IV
1 year ago

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

  • SamsonTruong's avatar
    SamsonTruong
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      han_rj
      Icon for Helper IV rankHelper IV
      1 year ago
      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's avatar
        SamsonTruong
        Icon for Super User rankSuper User
        1 year ago

        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