Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 months ago
Solved

Drill Through with comma separated values

Team, I’m trying to understand whether the following drillthrough behavior is achievable in Power BI. Scenario: I have a bar chart with 4 categories: A, B, C, D Drillthrough is en...
Need Help
  • v-saisrao-msft's avatar
    v-saisrao-msft
    8 months ago

    Hi Anonymous,

    The “Query has exceeded the available resources” error in Power BI occurs when the engine is unable to process a query due to limitations in memory, CPU, or execution capacity. This issue is related to the system's capacity or the complexity of the query, rather than a DAX syntax error.

    To resolve this issue:

    • Avoid using a measure as a visual-level filter on a detailed table.
    • Incorporate the logic into the data model by creating a calculated column, such as
    Has Affiliation Change =
IF (
    CONTAINSSTRING (
        'AFFILIATION CHANGE FLAG'[affiliation_change_category],
        RELATED ( 'Dim Affiliation Category Bridge'[affiliation_change_category] )
    ),
    1,
    0
)

    Use slicers or dimension tables to apply filters, rather than using measures.

     

    Thank you.

v-saisrao-msft's avatar
v-saisrao-msft
Icon for Community Support rankCommunity Support
8 months ago

Hi Anonymous,

According to Microsoft documentation, Power BI drillthrough uses exact-value filters based on relationships and does not allow partial matching. If categories are stored as comma-separated values, it is recommended to normalize the category dimension with a small bridge table so that filtering works through relationships. Keeping the fact table unchanged, especially for large datasets, follows Power BI’s star schema and many-to-many modeling best practices.

Many-to-many relationships in Power BI Desktop - Power BI | Microsoft Learn

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn
Drillthrough in Power BI Reports: Navigate to Detailed Insights - Power BI | Microsoft Learn

Thank you.

Anonymous's avatar
Anonymous
Not applicable
8 months ago

hi v-saisrao-msft ,
I followed the suggestion and the steps by lbendlin , however I am getting the below error as soon as I apply the measure on the detailed table visual.


Surprising part is that I don't see any error if I don't apply this filter. And after applying this measure, it asks me to filter some data to reduce it.

 

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi Anonymous,

    The “Query has exceeded the available resources” error in Power BI occurs when the engine is unable to process a query due to limitations in memory, CPU, or execution capacity. This issue is related to the system's capacity or the complexity of the query, rather than a DAX syntax error.

    To resolve this issue:

    • Avoid using a measure as a visual-level filter on a detailed table.
    • Incorporate the logic into the data model by creating a calculated column, such as
    Has Affiliation Change =
IF (
    CONTAINSSTRING (
        'AFFILIATION CHANGE FLAG'[affiliation_change_category],
        RELATED ( 'Dim Affiliation Category Bridge'[affiliation_change_category] )
    ),
    1,
    0
)

    Use slicers or dimension tables to apply filters, rather than using measures.

     

    Thank you.

  • v-saisrao-msft's avatar
    v-saisrao-msft
    Icon for Community Support rankCommunity Support
    8 months ago

    Hi Anonymous,

    Checking in to see if your issue has been resolved. let us know if you still need any assistance.

     

    Thank you.