Create slicer based on treemap

[belenfdez23]

Hi there!

I'm new in the Power BI Community, and I have a question. I have created a treemap whose values are the count of some elements (nodes), and a slicer on the same report in order to filter the info by date. The next step would be creating another slicer that filters all the visuals according to this count. In other words, I want to do the visual lever filter, but in a slicer, so I can choose what elements are shown in the treemap (for example, those that appear more than 20 times in the last week). How can I do that?

Here are some images to clarify this issue:

[prateekraina · Accepted solution]

Hi @belenfdez23,

Follow below steps:

1. Create following two measures to store the maximum and minimum values your data can produce so that we know the end values of our slicer.

Maximum Count = 
VAR CalcTable =
    SUMMARIZE (
        Table,
        Table[Category],
        "Count", COUNT ( Table[Category] )
    )
RETURN
    MAXX (
        CalcTable,
        [Count]
    )


Minimum Count = 
VAR CalcTable =
    SUMMARIZE (
        Table,
        Table[Category],
        "Count", COUNT ( Table[Category] )
    )
RETURN
    MINX (
        CalcTable,
        [Count]
    )

2. Now we need to create a calcuated table which will store our slicer values between the range. Go to Modeling > New Parameter. Press Ok. This will create a What-If Parameter table and add a slicer on the canvas automatically. Ignore the slicer for now.

3. Now go to the DAX query of this table and modify it to below:

  

Parameter = GENERATESERIES([Minimum Count], [Maximum Count], 1)

4.  Now we need to create the actual measure which will hold our dynamic count and will be used in the Treemap visual. Here is the code:

   

Count = 
CALCULATE (
    COUNT ( Table[Category] ),
    FILTER (
        Table,
        COUNT ( Table[Category] ) > Parameter[Parameter Value]
    )
)

 5. Now use the slicer earlier created. The value selected in this slicer will be used to filter the Treemap.

Note: Change columnn and table name as per yours, I have assumed your node column as my category column.

Prateek Raina

[prateekraina · Accepted solution]

Hi @belenfdez23,

Follow below steps:

1. Create following two measures to store the maximum and minimum values your data can produce so that we know the end values of our slicer.

Maximum Count = 
VAR CalcTable =
    SUMMARIZE (
        Table,
        Table[Category],
        "Count", COUNT ( Table[Category] )
    )
RETURN
    MAXX (
        CalcTable,
        [Count]
    )


Minimum Count = 
VAR CalcTable =
    SUMMARIZE (
        Table,
        Table[Category],
        "Count", COUNT ( Table[Category] )
    )
RETURN
    MINX (
        CalcTable,
        [Count]
    )

2. Now we need to create a calcuated table which will store our slicer values between the range. Go to Modeling > New Parameter. Press Ok. This will create a What-If Parameter table and add a slicer on the canvas automatically. Ignore the slicer for now.

3. Now go to the DAX query of this table and modify it to below:

  

Parameter = GENERATESERIES([Minimum Count], [Maximum Count], 1)

4.  Now we need to create the actual measure which will hold our dynamic count and will be used in the Treemap visual. Here is the code:

   

Count = 
CALCULATE (
    COUNT ( Table[Category] ),
    FILTER (
        Table,
        COUNT ( Table[Category] ) > Parameter[Parameter Value]
    )
)

 5. Now use the slicer earlier created. The value selected in this slicer will be used to filter the Treemap.

Note: Change columnn and table name as per yours, I have assumed your node column as my category column.

Prateek Raina

[belenfdez23]

Thank you! I tried to do what you suggested, but I have a problem with FILTER(Treemap,...). It says: 'Failed to resolve name 'Treemap'. It is not a valid table, variable, or function name.' Do I have to name my treemap in a specific way? If so, how should I do it?

Thanks again!!

Belén

[prateekraina]

Hi @belenfdez23,

I edited it to what it should be in my case (Highlighed in Bold). You should use your table name, the table which has nodes which you're counting.

Prateek Raina

[belenfdez23]

Thanks for all your help, @prateekraina.  I followed all your steps and it worked.

I have one extra issue here. I want the slicer to be between two values, not a list. How can I do that? If I just change the format of the visualization, it doesn't work, as the formula for the measure 'Count' depends on a single value of the parameter. 

Thank you again!!

Belén

[prateekraina · Accepted solution]

Hi @belenfdez23,

In order to do that I am assuming that whatever range you choose the count should be inclusive of those numbers.
Example: If you choose Range between 2 & 10, then it would show count for Categories who have count >=2 and <= 10.

So, here are the measures:

1. Make your slicer visual to show Range (which i guess you would have already done)
2. Add following two new measures:

Min Selected Value = 
CALCULATE (
    MIN ( Parameter[Parameter] ),
    ALLSELECTED ( Parameter[Parameter] )
)


Max Selected Value = 
CALCULATE (
    MAX ( Parameter[Parameter] ),
    ALLSELECTED ( Parameter[Parameter] )
)

3. Modify the COUNT measure to below:
    

Count = 
CALCULATE (
    COUNT ( Table[Category] ),
    FILTER (
        Treemap,
        COUNT ( Table[Category] ) >= [Min Selected Value]
            && COUNT ( Table[Category] ) <= [Max Selected Value]
    )
)

 4. Thats it !! Now your range slicer is ready to filter.

Prateek Raina

[belenfdez23]

THANKS!!!!

[prateekraina]

Hi @belenfdez23,

Will get back to you on this asap.

Prateek Raina