Forum Discussion

danisharoon's avatar
danisharoon
Helper I
3 years ago

Matrix - How to hard code some entries and one row corresponding to the slicer selection?

I have a matrix visualization consisting of three columns, brand name, this year revenue, and last year revenue. There are a total of 10 brands names, out of which only 8 are made to appear in the sl...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
danisharoon's avatar
danisharoon
Helper I
3 years ago

Hi v-yanjiang-msft 


Thanks for responding. Let me redefine the problem with an example. The tables are as follows:

 

I have created a slicer with brand names C,D, and E.

I would want the matrix to always show the data of A, and B, and whichever brand is selected using the slicer.

For example, if I select D from the slicer then the matrix should look as follows:

Danish

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi danisharoon ,

    Thanks for the explanation, I got it. I create a new sample:
    fact_revenue:

    dim_brand:

    slicer:

    fact_revenue and dim_brand tables are related with Brand ID column.

    Here's my solution, create a measure.

    Check =
IF (
    MAX ( 'dim_brand'[Brand Name] )
        IN { "A", "B" }
            || MAX ( 'dim_brand'[Brand Name] ) = SELECTEDVALUE ( slicer[slicer] ),
    1,
    0
)

    Put this measure in the visual filter and set its value to 1.

    After click apply filter, get the expected result.

    I attach my sample below for your 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.

     

    • danisharoon's avatar
      danisharoon
      Helper I
      3 years ago

      Hi v-yanjiang-msft 

       

      Thank you for the support. The proposed solution does fix the issue, however the problem is that the same slicer applies to other visuals as well. Hence instead of using the slicer table, I believe its wise to use the dim_brand table for the slicer. If I change the DAX query to:

       

      Check1 = IF(MAX('dim_brand'[Brand Name])IN {"A","B"}||MAX('dim_brand'[Brand Name])=SELECTEDVALUE(dim_brand[Brand Name]),1,0)
       
      Then its not working as required.
      Danish
      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi danisharoon ,

        The reason I create a new table is that we can't use the same column in the dim_brand table to achieve the result anyway. The visual will always be filtered "correctly" by the same column slicer. Only table without relationship can do it.

        For other visuals, you can also use measures as visual filter for filtering purposes. Or just use the new table to filter the hard code visual, then use dim_brand to filter other visuals, turn off the interaction between the hardcode visual and dim_brand slicer at the same time.

         

        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.