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

[RDKleinJan]

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 nature of the aggregations the list should be empty unless there is a single selection made in the slicer or other list (Summing the values up would cause a lot of confusion).

Users can select organizations in either a slicer or another list with other aggregations.

The idea is that upon opening the report, there are two lists a slicer, and a few other elements which are not relevant.

The first list is filled with organizations and data relevant to each organization, total sales, amount of items sold for examlpe.

The second list shows up empty at first.

Upon selecting a single organization in the first list or in the slicer, the second list loads the data, showing the organizations that the selected organizations has sold items to for example, and per organizations the amount of items, amount of revenue, etc.

The issue is that the data is shown when multiple organizations are selected. The amount of items sold or revenue per organization no longer makes sense when you don't have a single organization to refer to.

I've been trying to figure out a dax query ( With statements such as selectedvalue, hasonevalue, allselected) that can solve this issue, but it seems to be really difficult (Or i am just missing something :P).

I am trying to find out a way in the query if there is more than one organization selected, but from the record's point of view there is always only one organization linked to it. (It's a 1-* relationship.) In case of more than 1 or no selection(s), no data should be returned. This works in case of no selection made, the list shows up empty. But when two or more organizations are selected, the list still loads.

I could add another slicer and have it filter just that list, but since users would be slicing on the exact same thing as in the other slicer that is already present, it is not desirable. This would cause more confusion.

A matrix is also not an option because the amount of columns/rows would explode due to the amount of organization combinations being possible.

I don't have time to add a mockup pbix right now but could do so later if it could help presenting the issue.

Thank you for your time reading this 🙂 

[RDKleinJan]

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

OrgA	A
OrgB	B
OrgC	C
OrgD	D

Table organization sales

Relationship Organization Values

A	OrgX	100
A	OrgY	175
A	OrgZ	200
B	OrgY	100
B	OrgZ	50
C	OrgQ	300

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 · Accepted solution]

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]

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

[RDKleinJan]

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

[Greg_Deckler]

@RDKleinJan Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.