Forum Discussion

tmul's avatar
tmul
Frequent Visitor
5 years ago
Solved

Measure to use range slicer which can include blanks

Hi,  I would like to use a slicer [tick box] to allow the user to decide if he wants to see only the values from a range slicer or in addition blank values.  I have a dataset with a range of number...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi tmul ,

     

    Create a table with the values and another with the slicer:

    SLICER VALUES = FILTER( DISTINCT('Table (2)'[Value]), 'Table (2)'[Value] <> BLANK())

    This table should be used for the slicer of the values.

     

    Now add the following measure:

    Total Value =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "Yes"
        && SUM ( 'FACTTABLE'[Value] ) = BLANK (), "",
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "Yes"
        && CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        )
            <> BLANK (),
        CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        ),
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "No",
        CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        )
)

     

    Has you can see below and in the attach file the selection show and hides the blanks:

     

     

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
5 years ago

Hi tmul ,

 

Create a table with the values and another with the slicer:

SLICER VALUES = FILTER( DISTINCT('Table (2)'[Value]), 'Table (2)'[Value] <> BLANK())

This table should be used for the slicer of the values.

 

Now add the following measure:

Total Value =
SWITCH (
    TRUE (),
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "Yes"
        && SUM ( 'FACTTABLE'[Value] ) = BLANK (), "",
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "Yes"
        && CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        )
            <> BLANK (),
        CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        ),
    SELECTEDVALUE ( 'Show/Blanks'[Blank] ) = "No",
        CALCULATE (
            SUM ( 'FACTTABLE'[Value] ),
            'FACTTABLE'[Value] IN VALUES ( 'SLICER VALUES'[Value] )
        )
)

 

Has you can see below and in the attach file the selection show and hides the blanks:

 

 

 

tmul's avatar
tmul
Frequent Visitor
5 years ago

Tanks a lot for the help MFelix ! I could implement it in my report.  Now I am only in front of another rather small issue. The ID does not need to be unique. Then as I see it, the measure would sum up the different values for one ID. Do I need to use the SUMX command instead somehow or do you have another suggestion? 
Let's say that ID 1 appears two times, one times with 1 and one times with 0.9. Now the ID 1 would not be shown, but I would like to have it 🙂 The best thing would be if I could get all IDs from it which satisfy the filters for each row. That sounds like a SUMX or? 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi tmul ,

     

    This depends on the filtering do you want to compare the SUM of the ID compared to the slicer or the value of each ID.

     

    Has you can see keeping the same filters has previous 0,1 and 1.1 the value that appears is the 1.9

     

    However if you change the slicer from 1 to 2 for example it only shows 1:

    In this case do you want to appear 1 or 1.9 because is within the range.