Forum Discussion
How to use calculated columns to get data from Direct Query tables (or any other method)
Hi Anonymous,
Create a Disconnected Slicer Table:
Item Match Filter = DATATABLE( "Match Status", STRING, { {"Found"}, {"Not Found"} })Create a Measure to Determine Match Status:
Item Match Status = VAR SelectedStatus = SELECTEDVALUE('Item Match Filter'[Match Status]) RETURN SWITCH( SelectedStatus, "Found", IF( NOT ISBLANK( LOOKUPVALUE( Configuration[Item], Configuration[Item], SELECTEDVALUE(Activity[Item]) )), 1, 0 ), "Not Found", IF( ISBLANK( LOOKUPVALUE( Configuration[Item], Configuration[Item], SELECTEDVALUE(Activity[Item]) ) ), 1, 0), 1)Create a Table visual with the columns Item and Quantity from the Activity table. Apply a visual-level filter using the Item Match Status measure, setting it to 1. Next, add a slicer using 'Item Match Filter'[Match Status] to allow users to filter by "Found" or "Not Found".
I'm Attaching the file for your Reference
Thank you.
Hi Anonymous ,
Thank you for being a part of the Microsoft Fabric Community.
use the LOOKUPVALUE function, which works across DirectQuery tables. The formula checks if the Item in the Activity table exists in the Configuration table. If it’s found, the result will be "Yes"; if not, it will return "No".
Here's the DAX formula for the calculated column:
Item is found =
VAR CurrentItem = 'Activity'[Item]
VAR FoundItem =
LOOKUPVALUE(
Configuration[Item], // The column you are checking
Configuration[Item], CurrentItem // Matching condition
)
RETURN
IF(
ISBLANK(FoundItem),
"No",
"Yes"
)
After creating this calculated column, you can use it in a slicer to filter between "Yes" and "No" values. This should work both in Power BI Desktop and Power BI Service. However, be aware of potential performance issues in Power BI Service, especially when dealing with large datasets from DirectQuery sources. If performance becomes a concern, consider optimizing the queries or aggregating data at the source level.
I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
If this post helped, please mark it as "Accept as Solution" so others can benefit as well.
Best regards,
Sahasra.
Hey
Thank you for your response, however, this solution had the same problem. It works fine in desktop and in Power BI service until the data is refreshed and then the error appears that says something like this:
the query referenced a calculated column <oii>Activity</oii>[<oii>Item is found</oii>], which does not contain data because evaluating a row caused an error.
Any idea whats wrong?
Hi Anonymous ,
Since calculated columns in imported tables that reference DirectQuery tables are not supported during data refresh in Power BI Service, the most reliable and stable solution is to handle this logic in Power Query.
To implement this, open Power Query and select your Activity table. Then, perform a Left Outer Join with the Configuration table using the Item column as the matching key. After the join, expand the columns from the Configuration table as needed. Then, create a custom column that checks if the joined Configuration[Item] is null -- if it is, return "No"; otherwise, return "Yes".This new column can be named "Item is found" and will exist entirely in the imported table, making it suitable for use in slicers and visuals. Most importantly, this approach works consistently in both Power BI Desktop and Service, even after a data refresh.
If my response was helpful, consider clicking "Accept as Solution" and give us "Kudos" so that other community members can find it easily. Let me know if you need any more assistance!
Thank you.
Hi Anonymous,
We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!
Hey
Sorry for a bit late answer! Your solution could be helpful if I could import the Configuration table but it is a appended table from a different semantic model which consist of loads of different sources. Thats why I am using direct query for it. For now, I am just using a measure to filter down the table but thats not ideal since report users prefer using slicers for showing the error rows or the data as a whole. However, its good enough for now if nothing better comes up.