topic Re: Dax for select All in DAX Commands and Tips

Dax for select All

Kopek — Wed, 22 Jun 2022 11:56:29 GMT

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!

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 12:06:11 GMT

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

Re: Dax for select All

Kopek — Wed, 22 Jun 2022 12:09:51 GMT

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

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 12:22:35 GMT

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

Showcase Report – Contoso By SpartaBI

Re: Dax for select All

Kopek — Wed, 22 Jun 2022 13:21:11 GMT

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.

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 13:29:17 GMT

Kopek like this?

Look at the matrix on the left

Re: Dax for select All

Kopek — Wed, 22 Jun 2022 13:40:15 GMT

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:

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 13:51:09 GMT

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

Re: Dax for select All

Kopek — Wed, 22 Jun 2022 14:07:06 GMT

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 ?

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 14:45:37 GMT

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

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 14:50:35 GMT

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

Re: Dax for select All

Kopek — Wed, 22 Jun 2022 14:55:12 GMT

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

Re: Dax for select All

SpartaBI — Wed, 22 Jun 2022 15:31:26 GMT

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

Re: Dax for select All

Anonymous — Mon, 27 Jun 2022 07:41:14 GMT

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.