How to use calculated columns to get data from Direct Query tables (or any other method)

[Kasinaama]

I have imported tables and Direct Query tables. In one of my imported tables, I need to find if the certain column's values are found in direct query table's column or not. Then I need this information in slicer so I can filter either the found or not found rows on the table visual. However, I cannot seem to make my calculated columns work and measure is not good either because it can not be used in a slicer. I tried using a following calculated DAX formula which worked in desktop but not in Power BI Service.

Item is found =

VAR CurrentValue = 'Activity'[Item ]

RETURN

    IF(

        ISBLANK(CurrentValue),

        "No",

        IF(

            CALCULATE(

                COUNTROWS(Configuration),

                FILTER(

                    Configuration,

                    NOT ISBLANK(Configuration[Item]) &&

                    Configuration[Item] = CurrentValue

                )

            ) > 0,

            "Yes",

            "No"

        )

    )

Also I tried using RELATED function but it does not seem to find Direct Query table's column. Any help is appreciated!

[v-sgandrathi]

Hi @Kasinaama ,

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.

[Kasinaama]

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?

[v-sgandrathi]

Hi @Kasinaama ,

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.

[v-sgandrathi]

Hi  @Kasinaama,

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!

[Kasinaama]

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.

[v-sgandrathi]

Hi @Kasinaama,

Thanks for the clarification, and your workaround using a measure makes sense given the DirectQuery constraints. We understand the preference for slicers, especially for end-user experience, but limitations with cross-model DirectQuery do restrict options like importing configuration tables.

It’s good that your solution works for now, and we’ll be sure to share any future updates that improve filtering flexibility in these scenarios.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[v-sgandrathi]

Hi @Kasinaama,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

[Kasinaama]

Hey

Well I haven't found exact solution I was looking for but my approach with measures will do for now. I was planning to leave this open if in the future someone comes up with something else.

[v-sgandrathi · Accepted solution]

Hi @Kasinaama,

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.

[v-sgandrathi]

Hi @Kasinaama,

We haven’t heard from you on the last response and was just checking back to see if your query was answered.
Otherwise, will respond back with the more details and we will try to help .

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.