Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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

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!
Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    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.

10 Replies

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        v-sgandrathi
        Community Support
        1 year ago

        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.