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

[byrdwatcher1978]

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. 

[Icey]

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.

4. Test.

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.

[byrdwatcher1978]

@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.

[Icey]

Hi @byrdwatcher1978 ,

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

Best Regards,

Icey

[byrdwatcher1978]

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

[Icey]

Hi @byrdwatcher1978 ,

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

Best Regards,

Icey

[byrdwatcher1978]

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. 

[Icey]

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.

Best Regards,

Icey

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

[byrdwatcher1978]

@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. 

[Icey]

Hi @byrdwatcher1978 ,

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

Best Regards,

Icey

[byrdwatcher1978]

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

[Icey]

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

Best Regards,

Icey

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

[byrdwatcher1978]

@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. 

[Icey]

Hi @byrdwatcher1978 ,

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

Best Regards,

Icey

[byrdwatcher1978]

@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. 

[Icey · 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 )
    )

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]

@byrdwatcher1978 

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

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

[byrdwatcher1978]

The table looks something like this. 

Company	parentaccountid	accountid
Company A (Main Company)	abc	xyz
Company B (Subsiduary of A)	xyz	def
Company C (Subsiduary of A)	xyz	hij

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

[Greg_Deckler]

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

Category	Subcategory
One	Red
One	Green
Two	Blue

Then this should be trivial.