Add a dax formula to a slicer?

[Ncio]

Hello, 

I have a graph with a slicer that works well. I'd like to add one choice to the slicer but it's a "dax formula" and I'm not sure how to do it.

The slicer allows to select a type of training session (online, face to face, ...) and the graph displays the number of session over the year (year to date, current and past year)

Training sessions:

• Online
• Face to face
• Cancelled
• Active sessions (dax formula: basically "sum(all sessions) - sum(cancelled)" )

I understand that one solution would be to to calculate everything in a new calculated table using SUMMARIZE.

• Is it the most simple solution ? A best practice?

Note: I have no issue with the Dax, the question is really about adding the choice in the slicer.

Thank you,

[OwenAuger]

Hi @Ncio 

I would suggest you:

1. Create an additional table to act as the slicer, which includes the existing Training Session values plus the special value of "Active sessions". Here is how you could create the table using DAX:

Training Sessions = 
UNION ( 
    ALLNOBLANKROW ( ExistingTable[Training Session] ),
    { "Active sessions" }
)

2. Create a relationship between this new table and the existing table with the Training Session column.

3. Use the Training Session column of the new table on the slicer.

4. Write a new measure that handles the "Active sessions" option. Something like:

Number of Sessions =
IF ( 
    SELECTEDVALUE ( 'Training Sessions'[Training Session] ) = "Active sessions",
    <Special expression for Active sessions>, -- May need REMOVEFILTERS ( 'Training Sessions'[Training Session] )
    <Existing session count measure>
)

 Note that the special expression for Active sessions would likely need to remove the filter on 'Training Sessions'[Training Session] because the value "Active sessions" does not match any rows of your original table.

This article on Top & Other products covers something similar as far as model setup.

Reards,

Owen

[amitchandak · Accepted solution]

@Ncio , Not very clear. Are you looking for measure slicer

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://www.youtube.com/watch?v=vlnx7QUVYME

[Ncio]

Hello,

Yes, I will switch to a measure slicer to cover all the requirements including the "calculation"

Thank you,

[selimovd]

Hello @Ncio ,

you cannot add a measure to a slicer.

A measure always needs a context to give a result, like sum by location or by product category. So it wouldn't work in a slicer.

You can add values as a calculated column and use that as a slicer. Would that work for your case?

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Blog: WhatTheFact.bi