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

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



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

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

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

        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