Forum Discussion

ChazTime's avatar
ChazTime
Frequent Visitor
1 year ago
Solved

Direct Query Filtering Values in Visual based on another Columns Values

Hello, Using PBI Direct Query data, I have a table where I need to show specific (or group of specifc values) based on another columns values. Using a table visual, I want to only show the data th...
Need Help
  • v-pnaroju-msft's avatar
    v-pnaroju-msft
    1 year ago

    Hi ChazTime,

    Thank you for your detailed follow-up and your efforts in debugging the scenario.

    While the ShowVIS logic functions correctly in a straightforward setup, the inclusion of additional filters such as Loc ID in a DirectQuery model may cause the measure to malfunction. This is due to the manner in which DAX is evaluated and translated into SQL. When complex filters are applied or measures rely on CALCULATE logic involving related tables, Power BI’s query folding can behave unpredictably in DirectQuery mode. This may result in incorrect or uniform outputs, for example, all values being 1.

    Kindly consider the following workaround options which might assist in resolving the issue:

    1. Use a SQL view to pre-aggregate or filter the data at the source.
    2. Employ a composite model (Import + DirectQuery) for enhanced flexibility in DAX.

    If you find our response helpful, please mark it as the accepted solution and provide kudos. This will help other community members encountering similar queries.

    Should you have any further questions, please feel free to reach out to the Microsoft Fabric community.

    Thank you.

v-pnaroju-msft's avatar
v-pnaroju-msft
Icon for Community Support rankCommunity Support
1 year ago

Hi ChazTime,

Thank you for your response.

Since you have mentioned that switching from a calculated column to a measure did not resolve the issue, please consider the following points that may affect the behavior of the measure:

  1. Visual-Level Filter Application.After creating the measure (for example, ShowVIS = IF([DistinctNormalizedLots] > 1, 1, 0)), kindly ensure that it is added to the visual-level filters pane and set to display only when ShowVIS = 1.

  2. Normalized Lot Column.If you are working with Lot variations such as A617, A617R, and A617RV, it is necessary to create a normalized version of the Lot (for instance, by using Text.Start([Lot], 4) in Power Query) to ensure consistent grouping by VIS across these variations.

  3. Model Structure.If a related table is being used to map or normalize the Lots, please verify that the relationship is active and that the measure references the correct column from the appropriate table.

Please find the revised PBIX file attached, which may help to resolve the issue.

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may be facing similar queries.

Should you have any further questions, please feel free to contact the Microsoft Fabric community.

Thank you.