Forum Discussion

esingh's avatar
esingh
Icon for Helper I rankHelper I
1 year ago
Solved

filter selection as group in single visuals

Hi Community We have recieved a request to have following filter on a data set 1) Selected Company - User Choice 2) Company Cohort (Will be dynamically selected by User) 3) Classification of Comp...
Need Help
Tips and Tricks
  • grazitti_sapna's avatar
    grazitti_sapna
    1 year ago

    Hi esingh,

     

    Thanks for the clarification — since your Selected Company, Cohort, and Classification filters are coming from independent tables, the approach still works — you just need to make sure your DAX measure properly pulls selections from those disconnected filters and applies them against your main fact table via relationships or logic.

     

    Create Disconnected Comparison Group Table

    CompanyComparisonGroup =
    DATATABLE(
    "GroupType", STRING,
    {
    {"Selected Company"},
    {"Cohort"},
    {"Classification"},
    {"All Companies"}
    }
    )

     

    Create Your Metric Measure (Assuming measure Total Revenue)

    TotalRevenue = SUM('FactTable'[Revenue])

     

    Now define the unified logic using your disconnected slicers:

    CompanyGroupMeasure =
    VAR SelectedGroup = SELECTEDVALUE(CompanyComparisonGroup[GroupType])
    VAR SelectedCompany = SELECTEDVALUE('SelectedCompany'[CompanyName])
    VAR SelectedCohort = VALUES('Cohort'[CompanyName])
    VAR SelectedClass = VALUES('Classification'[CompanyName])

    RETURN
    SWITCH(
    TRUE(),
    SelectedGroup = "Selected Company",
    CALCULATE([TotalRevenue], 'FactTable'[CompanyName] = SelectedCompany),

    SelectedGroup = "Cohort",
    CALCULATE([TotalRevenue], 'FactTable'[CompanyName] IN SelectedCohort),

    SelectedGroup = "Classification",
    CALCULATE([TotalRevenue], 'FactTable'[CompanyName] IN SelectedClass),

    SelectedGroup = "All Companies",
    [TotalRevenue]
    )

     

     

    • Axis: CompanyComparisonGroup[GroupType]

    • Value: CompanyGroupMeasure

    • Optional Tooltip: Add [TotalRevenue] as well for raw value view

     

     

     

grazitti_sapna's avatar
grazitti_sapna
Icon for Super User rankSuper User
1 year ago

Hi esingh ,

 

This is absolutely achievable in Power BI using a combination of disconnected tables, field parameters, and DAX logic to create a unified visual with grouped bars for, use below DAX to create a disconnected table first:-

 

CompanyComparisonGroup =
DATATABLE(
"GroupType", STRING,
{
{"Selected Company"},
{"Cohort"},
{"Classification"},
{"All Companies"}
}
)

 

Make sure you have slicers for:

  • Company[CompanyName] — for selected company

  • Cohort[CohortName] — for selected cohort

  • Classification[ClassName] — for group of companies

Create the Measure to Calculate Values per Group

 

CompanyGroupMeasure =
VAR SelectedGroup = SELECTEDVALUE(CompanyComparisonGroup[GroupType])
VAR SelectedCompany = SELECTEDVALUE(Company[CompanyName])
VAR SelectedCohort = VALUES(Cohort[CohortName])
VAR SelectedClass = VALUES(Classification[ClassName])

RETURN
SWITCH(
TRUE(),
SelectedGroup = "Selected Company",
CALCULATE([YourMetric], Company[CompanyName] = SelectedCompany),

SelectedGroup = "Cohort",
CALCULATE([YourMetric], FILTER(Company, Company[Cohort] IN SelectedCohort)),

SelectedGroup = "Classification",
CALCULATE([YourMetric], FILTER(Company, Company[Classification] IN SelectedClass)),

SelectedGroup = "All Companies",
CALCULATE([YourMetric]),

BLANK()
)

 

Build the Visual

  • Use a bar chart.

  • Set the X-axis to CompanyComparisonGroup[GroupType].

  • Use CompanyGroupMeasure as the value.

 

 

🌟 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!