Forum Discussion

iW1DOV's avatar
iW1DOV
Icon for Helper II rankHelper II
1 year ago
Solved

How to filter for all or single value?

This is my requirement: I have a sales volumes table; there is a row for each sale a store made, for each day of the year. 
Each row is marked with a flag Comparable - 1 means "Comparable", 0 means "Not Comparable". 
The user wants the ability to see all data or Comparable data only. 

I can put a slicer that filters against the "Comparable " dimension, but it will show "Comparable" and "Not Comparable" choices; I can activate the "Select all" option. Unfortunately the user does not want the ability to filter for "Not Comparable" data only, because - being not comparable - by definition that data alone is meaningless.  He only wants the ability to see all data (comparable and not comparable altoghether) or Comparable ones only.  Something like this: 

 

Anyone has a suggestion on how to achieve this? I attach some sample data 

Note that in the sample report the "ALL" choice fiters for non comparable data, which is *NOT* the desired behaviour. 

Thanks in advance, 
Gianfranco 

EDIT: Sorry, I can't figure out how to attach the sample PBIX file, someone can teach me? Thank you

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    1 year ago

    iW1DOV,

     

    One approach is to use a field parameter. First create the two measures below, and then create a field parameter using these measures.

     

    All = SUM ( Sales[Amount] )
    Comparable only = CALCULATE ( [All], Sales[Comparable] = 1 )

     

    Field parameter (created via the user interface as explained in the article below):

     

    Parameter = {
    ("All", NAMEOF('Measure'[All]), 0),
    ("Comparable only", NAMEOF('Measure'[Comparable only]), 1)
}

     

    Use 'Parameter'[Parameter] in a visual:

     

     

    -----

     

     

    https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters 

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    1 year ago

    iW1DOV,

     

    One approach is to use a field parameter. First create the two measures below, and then create a field parameter using these measures.

     

    All = SUM ( Sales[Amount] )
    Comparable only = CALCULATE ( [All], Sales[Comparable] = 1 )

     

    Field parameter (created via the user interface as explained in the article below):

     

    Parameter = {
    ("All", NAMEOF('Measure'[All]), 0),
    ("Comparable only", NAMEOF('Measure'[Comparable only]), 1)
}

     

    Use 'Parameter'[Parameter] in a visual:

     

     

    -----

     

     

    https://learn.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters