Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
ChazTime
Frequent Visitor

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 that shares values in the "VIS" column. Due to work confidentiality I cannot share the pbix file, but example data and desired output below.

The key here, is I only want to show data that has more "Lots" associated to it than just the original. As an example A123 with v111 will show because it is associated to more lots, v122, v33 would not show.

 

For a little more context, this page utilizes a drillthrough so a customer can drill on the Lot and understand all the variants of the VIS and which other Lots are associated to the VIS. I have another data table that uses a connection to "normalize" the Lot values whether it ends in "r" or "rv".

Example Data

LotVIS
A123v111
A123rv111
A123rvv111
A123v122
A123v133
B124v144
B124rv144
B124rvv144
B125v155


Desired Output

LotVIS
A123v111
A123rv111
A123rvv111
B124v144
B124rv144
B124rvv144
1 ACCEPTED SOLUTION
v-pnaroju-msft
Community Support
Community Support

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.

View solution in original post

9 REPLIES 9
ChazTime
Frequent Visitor

Thank you for the solution and time @v-pnaroju-msft . I am looking at option 1 and if I have too, will change to import mode. Accepting solution.

v-pnaroju-msft
Community Support
Community Support

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
Community Support
Community Support

Hi ChazTime,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

Hello @v-pnaroju-msft ,

Sorry for the late response. I have been debugging for a few days. Initially your resolution worked but putting any additional filter completely breaks the table. The data set is very dynamic, there is an additional column used as a "Location" code, and adding that filter is what breaks the table. Additionally, when drilling through on different Lots, the table still is showing all data as the "ShowVis =1" filter is showing all values as a 1. I am not 100% sure why it is doing that but still debugging the issue. As an updated table this is the additional column. 

LotVISResultLocLoc ID
A6170001Pass6000X2
A6180002Fail6001X1
A618R0002Fail5001X1
A6180002Pass6002X3


Desired Output: (Added additional filter for Loc ID = X1 only

LotVISResultLocLoc ID
A6180002Fail6001X1
A618R0002Fail5001X1

 

I am tempted to close this and accept as a solution because I think in a more straight forward dataset this solution works, but with the direct query it does not. 

v-pnaroju-msft
Community Support
Community Support

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.

v-pnaroju-msft
Community Support
Community Support

Thankyou, @lbendlin, for your response.

Hi @ChazTime,

We sincerely appreciate your inquiry on the Microsoft Fabric Community Forum.

Kindly find attached the screenshot and PBIX file, which may assist in resolving the issue:

vpnarojumsft_0-1746466641527.png

If you find our response satisfactory, we would be grateful if you could mark it as the accepted solution and kindly provide kudos. This will help other community members who may have similar queries.

Thank you 

Hi @v-pnaroju-msft ,

Thank you for the response and pbix file. Unfortunelty your solution cannot work because I am using a Direct Query. The 'CALCULATE' function is not allowed. I changed from a calculated column to a measure but it still did not work. See error below. 


ChazTime_0-1746634596285.png

 

ChazTime
Frequent Visitor

Hi @lbendlin ,

I have tried that as well but most counts are showing as only a one, even if using distinct, even though it has multiple records. It might be do to the other columns I am adding to the table. For example expanding a bit, I could have VIS Results where its only showing 1x in the Lot but will also have a record with an R or RV Lot too. See below.

Also, the ending result isn't always a "Pass" could all fail too.

Example

LotVISResult
A617002Fail
A617R002Fail
A617RV002Fail
A617RV002Pass
A617001Fail
A617001Pass
A618003Pass

 

Desired Output

LotVISResult
A617002Fail
A617R002Fail
A617RV002Fail
A617RV002Pass

 

lbendlin
Super User
Super User

Change your point of view.  Group by VIS, and only show VIS that have a Lot count greater than 1.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.