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 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 🙂 

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.

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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.

  • 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
      Community 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.