Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Kopek
Helper IV
Helper IV

Dax for select All

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!

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

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.

vpollymsft_0-1656315571023.png

vpollymsft_2-1656315609582.png

vpollymsft_1-1656315593740.png

 

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.

 

View solution in original post

13 REPLIES 13
v-rongtiep-msft
Community Support
Community Support

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.

vpollymsft_0-1656315571023.png

vpollymsft_2-1656315609582.png

vpollymsft_1-1656315593740.png

 

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
Community Champion
Community Champion

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

SpartaBI_0-1655899536452.png

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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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

SpartaBI
Community Champion
Community Champion

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


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

@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
Community Champion
Community Champion

@Kopek like this?

SpartaBI_0-1655904500645.png

Look at the matrix on the left

@SpartaBI  Like this:

 

 

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

Kopek_0-1655905147779.png

 


Othrwise only whatever is selected should be visible:

 

Kopek_1-1655905195489.png

 

Kopek_2-1655905205731.png

 

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
Community Champion
Community Champion

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

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

SpartaBI_0-1655909416933.png

 


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

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
Community Champion
Community Champion

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

SpartaBI_1-1655911877226.png

 

 

SpartaBI
Community Champion
Community Champion

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

SpartaBI_0-1655905743549.png

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors