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 informati...
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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

Hi Anonymous,

 

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

    Hi Anonymous,

     

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        v-sgandrathi
        Community Support
        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.