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

     

     

     

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

Hey esingh ,

You can achieve this by creating dynamic measures with DAX and using filter context for each group. Use a slicer for the selected company, a dynamic filter for the cohort, predefined classifications in your data model, and an ALL() function for all companies. Combine these measures in a single bar chart, and use a SWITCH() or IF function in DAX to dynamically switch between the different groups based on the user's selection.

 

Measure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Company[Name]) = "Selected Company", [Measure for Selected Company],
    SELECTEDVALUE(Cohort[Name]) = "Company Cohort", [Measure for Cohort],
    SELECTEDVALUE(Classification[Category]) = "Classification", [Measure for Classification],
    [Measure for All Companies]
)

 

For Detailed Information:

Dynamic Measures in Power BI

DAX SWITCH Function

Using Filters in Power BI

ALL Function in DAX

 

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam