Forum Discussion

utkarshshastri's avatar
utkarshshastri
Helper I
10 months ago
Solved

numeric slicer for aggregated Y axis

 

 

I want to create a slicer filter where i can filter by count of accounts ? creating a parameter and measure to filter was not succesful filteration as it took lot of time . how can i created numeric slicer with mentioned info 

Need Help
  • Anonymous's avatar
    Anonymous
    10 months ago

    Hi utkarshshastri ,

    Thank you for the update.

    The “Resources Exceeded” message means Power BI is processing too much data at once, likely because SUMMARIZE with RELATEDTABLE is being used on the large Marketo_Leads and Marketo_Campaign_Activity tables.

    To fix this:

    Use Power Query’s “Group By” to pre-aggregate data by LEAD_MAIL_DMN_NME and create a summarized table (Lead_Count_Summary_PQ), which helps avoid heavy calculations at runtime.

    Alternatively, in DAX, limit summarization to more recent data, for example:

    Lead_Count_Summary =
VAR RecentLeads =
    FILTER (
        'Marketo_Leads',
        'Marketo_Leads'[MONTH_START] >= DATE(2024,1,1)
    )
RETURN
SUMMARIZE (
    RecentLeads,
    'Marketo_Leads'[LEAD_MAIL_DMN_NME],
    "LeadCount", COUNTROWS (RELATEDTABLE('Marketo_Campaign_Activity'))
)
​

    If your dataset is currently set to DirectQuery mode, consider changing it to Import mode to help lower the query load.

    Let us know if these changes resolve the issue, or if you continue to experience resource limitations. Hope this help you .

     

    Thank you.

8 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    10 months ago

    Hi utkarshshastri,

     

    Create a summary table in DAX:

    Account_Count_Summary =
    SUMMARIZE(
    'Accounts',
    'Accounts'[Hospital Name],
    "AccountCount", COUNT('Accounts'[AccountID]),
    "TotalSales", SUM('Accounts'[Sales])
    )

    1. Go to Model view.

    2. Create a relationship if needed (or use it standalone).

    3. Add a numeric slicer:

      • Add AccountCount from Account_Count_Summary.

      • Set slicer type to Between.

    4. Use this table in your bubble chart:

      • X-axis: [QoQ Growth] or any measure.

      • Y-axis: [AccountCount].

      • Size: [TotalSales].

      • Category: [Quadrant Category].

    This way, the slicer filters already aggregated data, which is instant.

     

    🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
    💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
    🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
    🔗 Curious to explore more? [Discover here].
    Let’s keep building smarter solutions together!

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi utkarshshastri ,

    Thank you for reaching out to the Microsoft fabric community forum.

     

    Could you please let us know if the issue has been resolved? I wanted to check if you had the opportunity to review the information provided by grazitti_sapna  . If you still require support, please let us know, we are happy to assist you.

     

    Thank you.

    • utkarshshastri's avatar
      utkarshshastri
      Helper I
      10 months ago

      hi Anonymous  not able use the below mentioned DAX , please use my SS to give me solution , let me know what is more required

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi utkarshshastri,

     

    Thank you for sharing the relationship details — that helps a lot.

    Based on your model (Marketo_Campaign_Activity → Marketo_Leads via LEAD_ID ↔ SRC_LEAD_IDN`), you can try creating a summarized table instead of using a measure. This approach improves performance and enables a numeric “Between” slicer directly.

    Lead_Count_Summary =
SUMMARIZE (
    'Marketo_Leads',
    'Marketo_Leads'[LEAD_MAIL_DMN_NME],
    "LeadCount", COUNTROWS (RELATEDTABLE('Marketo_Campaign_Activity'))
)

    Once after creating please do the following :

    1. Add LeadCount from this table into a slicer and set it to Between.
    2. Connect the slicer to your main visual using LEAD_MAIL_DMN_NME or the appropriate account field.
    3. This method filters pre-aggregated data, so it should be much faster than parameter-based filtering.

    Please note, since screenshots don’t include the data rows or DAX error details, we can’t fully reproduce the issue on our side, but the logic above aligns with your model structure.

    If you still face an issue, kindly share the exact error message or a few sample rows to help validate further.

    Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 months ago

        Hi utkarshshastri ,

        Thank you for the update.

        The “Resources Exceeded” message means Power BI is processing too much data at once, likely because SUMMARIZE with RELATEDTABLE is being used on the large Marketo_Leads and Marketo_Campaign_Activity tables.

        To fix this:

        Use Power Query’s “Group By” to pre-aggregate data by LEAD_MAIL_DMN_NME and create a summarized table (Lead_Count_Summary_PQ), which helps avoid heavy calculations at runtime.

        Alternatively, in DAX, limit summarization to more recent data, for example:

        Lead_Count_Summary =
VAR RecentLeads =
    FILTER (
        'Marketo_Leads',
        'Marketo_Leads'[MONTH_START] >= DATE(2024,1,1)
    )
RETURN
SUMMARIZE (
    RecentLeads,
    'Marketo_Leads'[LEAD_MAIL_DMN_NME],
    "LeadCount", COUNTROWS (RELATEDTABLE('Marketo_Campaign_Activity'))
)
​

        If your dataset is currently set to DirectQuery mode, consider changing it to Import mode to help lower the query load.

        Let us know if these changes resolve the issue, or if you continue to experience resource limitations. Hope this help you .

         

        Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 months ago

    Hi utkarshshastri ,

     

    I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 


    Thank you.