Dax for select All

[Kopek]

Hey!

I want to introduce "Select All" option in a clicket slicer.

I do have a list of qtrs like below.
What i want to achieve is - when "Select All" is clicked all available qtrs would be visible, otherwise only relevant qtr would be visible (if q1 2020 selected only data for q1 would be visible)

Could you please help me in writing this dax code ?

Quarter

Q1 2018

Q2 2018

Q3 2018

Q4 2018

Q1 2019

Q2 2019

Q3 2019

Q4 2019

Q1 2020

Q2 2020

Q3 2020

Q4 2020

Q1 2021

Q2 2021

Q3 2021

Q4 2021

Q1 2022

Q2 2022

Q3 2022

Q4 2022

Select All

Thanks in advance!

[v-rongtiep-msft · Accepted solution]

Hi @Kopek ,

Please refer to my pbix file wo see if it helps you.

Create a new table based on the table.

Table_copy = SUMMARIZE('Table','Table'[Quarter])

Then put the table_copy[Quanter] into the slicer.

Then create a measure.

Measure =
IF (
    SELECTEDVALUE ( Table_copy[Quarter] ) = "Select All"
        && SELECTEDVALUE ( 'Table'[Quarter] ) <> "Select All",
    1,
    IF (
        SELECTEDVALUE ( 'Table'[Quarter] ) = SELECTEDVALUE ( Table_copy[Quarter] ),
        1,
        0
    )
)

Then filter the measure.

Best Regards

Community Support Team _ Polly

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

[SpartaBI]

@Kopek you have that cabailty built in the slicer visual settings:

Just toggle it on and you will have select all in the options of the slicer.

      

Showcase Report – Contoso By SpartaBI

[Kopek]

Unfortunately i want to use the chiclet slicer, since need to format it differently, and it does not have select all option availabale.

[SpartaBI]

@Kopek please find the solution to do what you need:
Dax for select All.pbix

      

Showcase Report – Contoso By SpartaBI

[Kopek]

@SpartaBI thanks for that, but it's not exactly what i need!

I use qtrs as a matrix headers, so in this case i would need to have a list of all availabale qtrs when select all is hit, whereas when only one qtr is selected i want to see only this particular enrty in the matrix.

[SpartaBI]

@Kopek like this?

Look at the matrix on the left

[Kopek]

@SpartaBI  Like this:

When Selec All, all available qtrs should be visible in the table/ matrix - cannot figure out this part of measure.

Othrwise only whatever is selected should be visible:

[Kopek]

Yes, but it works only when measure is added agains qtrs, whereas i need a pure list of qtrs.
If SelectedValue = "Select All" give me full qtr list, otherwise show whatever selected. 🙂

Does it make any sense ? 

[SpartaBI]

@Kopek ye I see what you mean :))
I will PM you

[SpartaBI]

@Kopek if we stay in the box 🙂 we can leave the measure and just hide it (make the column headers and values not wrap and jsut minimize it to not be shown) - the measure is actually there:

      

Showcase Report – Contoso By SpartaBI

[Kopek]

will not work either, since i use qtrs as a matrix columns 🙈 that's why i need to figure-out a measure which will filter qtrs accordinly, so that values in a matrix are updated accoridngly

[SpartaBI]

@Kopek If we will keep the measure but just make the color of the values white? 🙂 Like this:

[SpartaBI]

@Kopek I think it's exactly what I showed you 🙂

Check it out in the file I shared, just create that additional matrix.
I updated the file there. Downlaod it again:
Dax for select All.pbix
Appreciate your kudos :))