Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Select value in slicer 1 and then a value shows in slicer 2

Hi, 

 

I have 2 slicers, one with a list of all companies and one with only subsiduary companies. The filtering is done as follows: slicer 1 has values for parentaccountid and slicer 2 has only blank values (both slicers have data from the same entity in dynamics 365). I would like to select a company in slicer 1 and then see the subsiduary companies in slicer 2 (they should all have the same parentaccountid). Does anyone know how I could achieve this?

 

Thanks. 

1 ACCEPTED SOLUTION

Hi @byrdwatcher1978 ,

 

Selection table:

Selection =
SUMMARIZE (
    'Table',
    'Table'[_parentaccountid_value],
    'Table'[name],
    'Table'[accountid]
)

Put this measure on the first slicer.

Is eq Selected ParentAccountID 2 = 
VAR SelectedAccountID =
    CALCULATE (
        MAX ( 'Selection'[_parentaccountid_value] ),
        FILTER (
            ALLSELECTED ( 'Selection' ),
            'Selection'[name] = SELECTEDVALUE ( 'Table'[name] )
        )
    )
VAR IsBlank_ =
    IF ( MAX ( 'Selection'[_parentaccountid_value] ) = BLANK (), 1 )
RETURN 
    IF (
        SELECTEDVALUE ( 'Table'[name]) = BLANK (),
        IsBlank_,
        IF ( MAX ( 'Selection'[accountid]) = SelectedAccountID, 1 )
    )

selection1.PNG

 

 

Best Regards,

Icey

 

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

View solution in original post

18 REPLIES 18
Icey
Community Support
Community Support

Hi @byrdwatcher1978 ,

 

Please check if this is what you want:

 

1. Create another table for selection.

Selection = VALUES ( 'Table'[parentaccountid] )

 

2. Create a measure for filtering.

Is eq Selected ParentAccountID = 
VAR SelectedAccountID =
    CALCULATE (
        MAX ( 'Table'[accountid] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[parentaccountid] = SELECTEDVALUE ( 'Selection'[parentaccountid] )
        )
    )
RETURN
    IF ( MAX ( 'Table'[parentaccountid] ) = SelectedAccountID, 1 )

 

3. Put the measure above on Slicer of 'Table'[Company], filtered with Is [eq Selected ParentAccountID] = 1.

selection.PNG

 

4. Test.

selection.gif

For more details, please check the attached PBIX file.

 

 

Best Regards,

Icey

 

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

@Icey , thanks. So I added this measure: 

 

Is eq Selected _parentaccountid_value =
VAR SelectedAccountID =
CALCULATE (
MAX ( 'accounts'[accountid] );
FILTER (
ALLSELECTED ( 'accounts' );
'accounts'[_parentaccountid_value] = SELECTEDVALUE ( 'Selection'[_parentaccountid_value] )
)
)
RETURN
IF ( MAX ( 'accounts'[_parentaccountid_value] ) = SelectedAccountID; 1 )
 
 I added the table as follows: 
Selection = SUMMARIZE(accounts; accounts[_parentaccountid_value]; accounts[accountid]; accounts[name] )
 
 But when I add the measure to the slicer, the slicer just goes blank.
 
Thanks.

Hi @byrdwatcher1978 ,

 

It works well on my side. Please select one value in Slicer 1 and check it again.

 

 

Best Regards,

Icey

Could you reattach the pbix file? I can't see it. Thank you. 

Hi @byrdwatcher1978 ,

 

Sorry, I seem to forget to attach the file. Please check.

 

 

Best Regards,

Icey

 

 

Hi @Icey 

 

Thanks. I would however like that the company is shown in both tables. The table on the right should be shown and then get filtered when I click on the left on company A for example. Thanks for your help. 

screen02.JPG

Hi @byrdwatcher1978 ,

 

Please check:

 

1. Change the Selection table.

Selection = VALUES('Table'[Company])

 

2. Change the [ Is eq Selected ParentAccountID ] measure like so:

Is eq Selected ParentAccountID = 
VAR SelectedAccountID =
    CALCULATE (
        MAX ( 'Table'[accountid] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Company] = SELECTEDVALUE ( 'Selection'[Company] ) -------changed
        )
    )
RETURN
    IF ( MAX ( 'Table'[parentaccountid] ) = SelectedAccountID, 1 )

 

3. Change the slicer.

filter2.PNG

 

 

Best Regards,

Icey

 

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

@Icey thank you. One last question, so the selection slicer should show only parentaccountid's where the value is blank and the selection slicer should show where the parentaccountid is not blank. As soon as I add the not blank filter to the selection slicer, the data disappears. How could i get around this? Thanks again. 

Hi @byrdwatcher1978 ,

 

I do not quite understand. Can you share some screenshots to help me better understand your problem?

 

 

Best Regards,

Icey

@Icey  I sent you a private message with my data. Thanks. 

Hi @byrdwatcher1978 ,

 

If you want to filter out blank values, change the [Is eq Selected ParentAccountID] measure like so:

Is eq Selected ParentAccountID = 
VAR SelectedAccountID =
    CALCULATE (
        MAX ( 'Table'[accountid] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[name] = SELECTEDVALUE ( 'Selection'[name] )
        )
    )
VAR NotBlank_ =
    IF ( MAX ( 'Table'[_parentaccountid_value] ) <> BLANK (), 1 )
RETURN
    IF (
        SELECTEDVALUE ( Selection[name] ) = BLANK (),
        NotBlank_,
        IF ( MAX ( 'Table'[_parentaccountid_value] ) = SelectedAccountID, 1 )
    )

filter blank.PNG

 

Best Regards,

Icey

 

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

@Icey  The slicers now look good but the selection doesn't work. If for example, I choose customer 310 with accountid of 2785a61b-a327-e811-812b-e0071b6e2511 in slicer 1, it should automatically show customer 327, 328, 329 and 330 (all with _parentaccountid_value of 2785a61b-a327-e811-812b-e0071b6e2511 in slicer 2 and vice versa. Thanks again. 

Hi @byrdwatcher1978 ,

 

It works well on my side. Please check the PBIX file in your Private Message.

selection.gif

 

Best Regards,

Icey

@Icey That's great, thanks. Is it also possible to select from the smaller slicer and get the desired result from slicer 1? Thanks again. 

Hi @byrdwatcher1978 ,

 

Selection table:

Selection =
SUMMARIZE (
    'Table',
    'Table'[_parentaccountid_value],
    'Table'[name],
    'Table'[accountid]
)

Put this measure on the first slicer.

Is eq Selected ParentAccountID 2 = 
VAR SelectedAccountID =
    CALCULATE (
        MAX ( 'Selection'[_parentaccountid_value] ),
        FILTER (
            ALLSELECTED ( 'Selection' ),
            'Selection'[name] = SELECTEDVALUE ( 'Table'[name] )
        )
    )
VAR IsBlank_ =
    IF ( MAX ( 'Selection'[_parentaccountid_value] ) = BLANK (), 1 )
RETURN 
    IF (
        SELECTEDVALUE ( 'Table'[name]) = BLANK (),
        IsBlank_,
        IF ( MAX ( 'Selection'[accountid]) = SelectedAccountID, 1 )
    )

selection1.PNG

 

 

Best Regards,

Icey

 

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

amitchandak
Super User
Super User

@byrdwatcher1978 

Refer this, How fact measure has been used to filter the slicer

https://www.youtube.com/watch?v=cyOquvfhzNM

The table looks something like this. 

 

Companyparentaccountidaccountid
Company A (Main Company)abcxyz
Company B (Subsiduary of A)xyzdef
Company C (Subsiduary of A)xyzhij

 

How would i go about creating the measure? Thank you. 

Greg_Deckler
Super User
Super User

Sample data please. For example, if your data is like this:

 

Category Subcategory
One Red
One Green
Two Blue

 

Then this should be trivial. 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.