topic check if anything selected in Slicer, then do sum based on selected Slicer values in DAX Commands and Tips

check if anything selected in Slicer, then do sum based on selected Slicer values

powerbeye — Sat, 21 Oct 2023 00:43:00 GMT

Hi,

I'm new to DAX and cannot find a solution to this problem I'm having.

I've come up with a Measure formula that is only partially working unfortuntely.

I have a data table in my report:

Manager	Activity	CostCategory	Year	Month	AnnualBudget
JohnDoe	IT	800	2023	1	$250
JohnDoe	IT	900	2023	1	$50
JohnDoe	IT	800	2023	2	$250
JohnDoe	IT	900	2023	2	$50

I have four slicers that users select from:
Manager, Activity, Year, Month

I have measure that calculates Total of Annual budget based on those slicer selections and shows the Sum value on Card.
For example, if I have Slicers selected like below:
Manager=JohnDoe, Activity=IT, Year=2023, Month=1, then my measure value would be $300 on the Card which is correct.

The problem I'm having is when a Month is NOT selected the Card shows $600 - the sum of all Months.
Since budget amount is set in the begining of year and doesnt change over month till end of year, I want it to sum only for single month, not all 12 months.

What I would like:
- if nothing selected on Slicer, show message "Please select from criteria first" on the Card
- if only Slicer Manager, Activity are selected, show message "Please select a year also" on the Card
- if Slicer Manager, Activity, Year are selected, show $300 on the Card (the sum value of single month only)
- if all selected, show $300 on the Card

My measure code is only working correctly when Month is also selected, otherwise it's multiplying that amount which I dont want.

FinalAnnualBudget = var ManagerSelected = SELECTEDVALUE(Expense[Manager], "") var ActivitySelected = SELECTEDVALUE(Expense[Activity], "") var YearSelected = SELECTEDVALUE(Expense[FISCAL_YEAR], "") var val = IF(AND(NOT ISBLANK(ActivitySelected), NOT ISBLANK(YearSelected)), "$" & CONVERT(SUM(Expense[Annual_Budget]), STRING), "Please select from criteria first") return val

Another issue I'm having is when I show the message "Please select from criteria first", it's not fitting inside the Card because the Card is formatted to show Currency. Is it possible to also format the message by making the text size smaller and change the color in Measure code or in some other way?

Thank you.

Re: check if anything selected in Slicer, then do sum based on selected Slicer values

ThxAlot — Sat, 21 Oct 2023 03:34:08 GMT

Try incoporating ISFILTERED() function into those criteria of your measure.

Re: check if anything selected in Slicer, then do sum based on selected Slicer values

Anonymous — Mon, 23 Oct 2023 05:53:41 GMT

Hi powerbeye ,

Here are the steps you can follow：

1. Create measure.

Measure = SWITCH( TRUE(), ISFILTERED('Table'[Manager])&&ISFILTERED('Table'[Activity])&&NOT(ISFILTERED('Table'[Year]))&&NOT(ISFILTERED('Table'[Month])) ,"Please select a year also", ISFILTERED('Table'[Manager])&&ISFILTERED('Table'[Activity])&&ISFILTERED('Table'[Year])&&NOT(ISFILTERED('Table'[Month])),SUMX(FILTER(ALLSELECTED('Table'),'Table'[Month]=MAX('Table'[Month])),[AnnualBudget]), ISFILTERED('Table'[Manager])&&ISFILTERED('Table'[Activity])&&ISFILTERED('Table'[Year])&&ISFILTERED('Table'[Month]),SUMX(ALLSELECTED('Table'),[AnnualBudget]), "Please select from criteria first" )

2. Result:

Best Regards,

Liu Yang

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

Re: check if anything selected in Slicer, then do sum based on selected Slicer values

Anuja_Chaudhari — Tue, 30 Jan 2024 10:48:00 GMT

select = SWITCH(

true(),

ISFILTERED(buget[Manager])&&ISFILTERED(buget[activity])&&not(ISFILTERED(buget[year]))&&not(ISFILTERED(buget[Month]))," Please select year also ",

ISFILTERED(buget[Manager])&&ISFILTERED(buget[activity])&&ISFILTERED(buget[year])&&NOT(ISFILTERED(buget[Month])),SUMX(FILTER(ALLSELECTED(buget),buget[Month]=MAX(buget[Month])),buget[annual buget ]),

ISFILTERED(buget[Manager])&&ISFILTERED(buget[activity])&&ISFILTERED('buget'[year])&&ISFILTERED(buget[Month]),SUMX(ALLSELECTED(buget),[annual buget ]),

"Please select from criteria first")

Re: check if anything selected in Slicer, then do sum based on selected Slicer values

Useranu — Fri, 04 Apr 2025 18:03:04 GMT

Can we show message "please select brand" inside Barchart and once the user selects Brand from filter the message should be gone and the data should show.

Please suggest.

Thank you