Forum Discussion

swatcxgp's avatar
swatcxgp
New Member
1 year ago

Syncing filters

Hi All,
 
Thank you in advance for the suggestions, and please let me know if any additional details would be helpful. 
 
I'm syncing filters on a dashboard for the same field names from two different queries. Initially, I tried creating a table and establishing relationships, but Power BI didn't like that.
 
Instead, I synced slicers for market, product line, sales rep, and estimator ID across budget and actual sales amounts.
The slicers for market name, product line, and sales rep work fine. However, the estimator ID slicer only connects to the sales filter, not the estimates filter.
 
For the method: I synced the sales - sales rep filter to the estimates sales rep filter, and the combined filter to the sales - sales rep filter. That seems to work for all categories except for the estimator ID.
 
Has anyone experienced similar issues of filters not syncing as expected? All data types are consistent between each filter.
 
Here's the code for the product line cleaned, working as expected:


Product Line Cleaned =IF(ISBLANK([Sales - Product Line]),

"Unknown",FORMAT([Sales - Product Line], "0"))

 

And the code for estimator ID cleaned, not working as expected:
 

Estimator ID Cleaned =IF(ISBLANK([Sales - Estimator ID]),

"Unknown",FORMAT([Sales - Estimator ID], "0"))

 

Need Help

7 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi swatcxgp -can you try the below code for estimator id cleaned. 

     

    Estimator ID Cleaned =
    IF(
    ISBLANK([Sales - Estimator ID]),
    "Unknown",
    [Sales - Estimator ID]
    )

     

    Replace null values consistently across both datasets to avoid mismatches.

     

    or with coalease() also works

    Estimator ID Cleaned =
    COALESCE([Sales - Estimator ID], "Unknown")

     

     

    Please try and let know.

    • swatcxgp's avatar
      swatcxgp
      New Member
      1 year ago

      HI rajendraongole1 Those formulas unfortunately don't solve the issue, it looks like they clean the data in the same manner but don't have an impact on the filters' behaviors.

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    Potential Causes and Solutions

    Here are a few potential reasons why the Estimator ID filter might not be syncing correctly:

    Data Type Mismatch:

    Double-check the data types for the Estimator ID field in both data sources. Ensure they are identical (e.g., both text or both numeric).
    Consider using a consistent format for the ID values in both sources to avoid potential discrepancies.
    Relationship Issues:

    Verify the relationships between the tables in your data model. Ensure that the relationship between the Sales and Estimates tables is correct and that it includes the Estimator ID field.
    If necessary, create a calculated column in one of the tables to bridge the relationship between the Estimator ID fields, especially if the formats or naming conventions differ slightly.
    Filter Context Issues:

    Analyze the filter context in your visuals and measures. Ensure that the Estimator ID filter is applied correctly and that there are no conflicting filters affecting its behavior.
    Use the ALL() or ALLEXCEPT() functions in your DAX measures to control the filter context as needed.
    Slicer Settings:

    Check the slicer settings for the Estimator ID slicer. Ensure that it is configured to filter both the Sales and Estimates data sources.
    Experiment with different slicer settings (e.g., single select, multi-select) to see if they affect the behavior.
    Code Review and Suggestions

    While the provided DAX code for Product Line Cleaned seems correct, it might be helpful to review the Estimator ID Cleaned code to ensure it's not causing any unintended consequences:


    Estimator ID Cleaned =
    IF(
    ISBLANK([Sales - Estimator ID]),
    "Unknown",
    FORMAT([Sales - Estimator ID], "0")
    )


    Consider using a more specific format for the Estimator ID if needed, depending on the desired output.
    If the Estimator ID is a numeric value, you might want to adjust the format to avoid unnecessary formatting.

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!

    • swatcxgp's avatar
      swatcxgp
      New Member
      1 year ago

      Thanks for the notes Saud! For some reason, after following all of your steps, the filter still behaves the same way. 

       

      I tried running the filters off a different query (I have query1 for sales and query2 for estimates) and then syncing those filters, but I'm still seeing the same behavior, just for the estimates graph instead. 

       

      Not sure why it would work for only 3/4 datesets! 

      • Irwan's avatar
        Irwan
        Super User
        1 year ago

        hello swatcxgp 

         

        perhaps the slicer visual interaction is off to that particular "not-synced" visual?

        i am not sure since interaction is ON by-default but it might be worth to check?

         

        Thank you.