Forum Discussion

RRSSDW's avatar
RRSSDW
Icon for Helper I rankHelper I
3 months ago
Solved

Slicer by logic conditions

Kindly I am asking if you have a smarter solution. Question is quite generic. Want to place a single selection slicer that has to show two options only, although the field includes many options. Hav...
Need Help
  • OwenAuger's avatar
    OwenAuger
    3 months ago

    Hi RRSSDW 

    To handle this kind of requirement with filter options consisting of overlapping sets, I would usually set up the model as follows (using your example):

    1. Creating a Type dimension (if it doesn't already exist).
    2. Create a Type Filter table containing the combinations of required filter options and corresponding Type values. Type Filter = "A"..."E" correspond to individual Type values of the same name, while Type Filter = "All Content" corresponds to Type = "A"..."E".
    3. Create bidirectional many-to-one relationship between 'Type Filter'[Type] and Type[Type].
    4. Use 'Type Filter'[Type Filter] on the slicer.
    5. Apply a page-level or report-level filter on 'Type Filter'[Type Filter] to limit the options as required, e.g. "A" and "All Content".

    In effect, this models a many-to-many relationship between Type and Type Filter. This avoids having to handle the filtering via DAX.

    This is similar modelling used for overlapping date ranges in this blog post.

     

    Small example PBIX attached.

     

     

     

d_m_LNK's avatar
d_m_LNK
Icon for Super User rankSuper User
3 months ago

What are the slicers helping quantify on the page?  I am guessing they help filter measures of somekind as they want to see totals for A only and then total for all types.  My suggestion might be to take away the selection and just make two measures that total those specific things.  
A Total = CALCULATE(MeasureName, Type = A)

You could either just the next one make this the overall total or specify each type in a calculate:

All Content Total = CALCULATE(MeasureName, Type IN {A,B,C,D})

 

Not sure how many measures you have on the page but just a different way to see the same breakdown -- this way you would also be able to see those totals side by side and also create other comparison measure for the totals.