Forum Discussion

RDKleinJan's avatar
RDKleinJan
Frequent Visitor
4 years ago
Solved

Problems with hiding data unless single selection in slicer. Data is shown upon multiple selections

Hi guys, I am having trouble with a report I'm making.  My goal is to have a table visual displaying a list of organizations and then aggregations for each of those organizations.   Due to the n...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi RDKleinJan ,

    I download your sample, as the two tables have relationship, when you select mutiple values in the slicer, the List2 visual will always be filtered be the slicer, so it can't be blank.

    Here's my solution.

    1.Create a new table by the following formula.

    Table = VALUES('Organization sales'[Organization])

    Get this table, it should has no relationship with other tables.

    2.Create a measure.

    Aggregate values = 
IF (
    ISFILTERED ( 'Organizations'[Organization] )
        && CALCULATE (
            DISTINCTCOUNT ( 'Organizations'[Organization] ),
            ALLSELECTED ( 'Organizations'[Organization] )
        ) = 1,
    MAXX (
        FILTER (
            ALLSELECTED ( 'Organization sales' ),
            'Organization sales'[Organization] = MAX ( 'Table'[Organization] )
        ),
        'Organization sales'[Values]
    ),
    BLANK ()
)

    3.Put the Organization column which is in the new table and the measure into table visual, get the expected result.

    I attach the sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RDKleinJan's avatar
RDKleinJan
Frequent Visitor
4 years ago

Thanks for your reply, I will add sample data in this post.

There are two tables, linked with a one to many cardinality

 

 

Table organizations

 

Organization    Relationship

OrgAA
OrgBB
OrgCC
OrgDD

 

Table organization sales

 

Relationship Organization Values

AOrgX100
AOrgY175
AOrgZ200
BOrgY100
BOrgZ50
COrgQ300

 

The second table also contains the following measure

 

 

 

 

Aggregate values = IF(ISFILTERED('Organizations'[Organization]) && CALCULATE(DISTINCTCOUNT('Organizations'[Organization]), ALLSELECTED('Organizations'[Organization])) == 1, CALCULATE(SUM('Organization sales'[Values])),BLANK())

 

 

 

 

Here's a link to download the PBIX.

https://www.mediafire.com/file/brx387gghfacdii/org_issue.pbix/file

 

There is a slicer in the report and two lists.

 

Slicer slices on field Organizations[Organization]

 

List 1 contains field Organizations[Organization]

 

List 2 contains fields Organization sales[Organization] and the earlier listed measure.

 

 

What I want to achieve:


1. Open the file

2. List 2 is empty as there is no selection made

3. Select any organization in either the slicer or in list 1

4. List 2 now loads the data relating to that organization

5. Deselect the organization

6. List 2 is empty again

7. Select multiple organizations from either the slicer or in list 1

8. List 2 is empty

 

And this last step is where it goes wrong unfortunately, it will still show the data relating to both organizations while it should remain empty.

 

I hope this provides some context 🙂 

 

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi RDKleinJan ,

    I download your sample, as the two tables have relationship, when you select mutiple values in the slicer, the List2 visual will always be filtered be the slicer, so it can't be blank.

    Here's my solution.

    1.Create a new table by the following formula.

    Table = VALUES('Organization sales'[Organization])

    Get this table, it should has no relationship with other tables.

    2.Create a measure.

    Aggregate values = 
IF (
    ISFILTERED ( 'Organizations'[Organization] )
        && CALCULATE (
            DISTINCTCOUNT ( 'Organizations'[Organization] ),
            ALLSELECTED ( 'Organizations'[Organization] )
        ) = 1,
    MAXX (
        FILTER (
            ALLSELECTED ( 'Organization sales' ),
            'Organization sales'[Organization] = MAX ( 'Table'[Organization] )
        ),
        'Organization sales'[Values]
    ),
    BLANK ()
)

    3.Put the Organization column which is in the new table and the measure into table visual, get the expected result.

    I attach the sample below for reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • RDKleinJan's avatar
      RDKleinJan
      Frequent Visitor
      4 years ago

      v-yanjiang-msft Thank you so much! That did the trick. You are a wizard 🙂 

  • RDKleinJan's avatar
    RDKleinJan
    Frequent Visitor
    4 years ago

    Anyone have a clue whether or not this is possible to achieve? Greg_Deckler