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 ,

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

 

 

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

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

    This is removing country and occupation entirely on Computers