Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am trying to filter the count of submissions by year and months selected by user (slicer),
I created a measure to get the count by year but in case of months, the end user can select multiple values (Jan, feb etc)
I wrote the following formula, but it only works when selecting one month not multiple
Submissions_CurrentyearValue =
var YearValue= MAX(DimDateSubmission[Year])
var MonthValue= ALLSELECTED('DimDateSubmission'[Month])
Return
Calculate(CountA(Submisions[Submission Number]),
Filter (
All(Submisions), YEAR(Submisions[Submission Date]) = YearValue && Month( Submisions[Submission Date]) = MonthValue ) )
Is there any way to allow multiple selections on month slicer??
Solved! Go to Solution.
Hey @Manar
I think replacing the "=" with "IN" will solve your problem, so instead of
&& Month( Submisions[Submission Date]) = MonthValue
you might want to try
&& Month( Submisions[Submission Date]) IN MonthValue
Let me know if that solves your problem.
Hope this helps,
Parker
Hey @Manar
I think replacing the "=" with "IN" will solve your problem, so instead of
&& Month( Submisions[Submission Date]) = MonthValue
you might want to try
&& Month( Submisions[Submission Date]) IN MonthValue
Let me know if that solves your problem.
Hope this helps,
Parker
Hi all,
I have a similar issue, but instead I need to get the month with the MAX and MIN net sales from current year per selected Status type. The user can choose among 3 categories (individually or clicking 2 or all 3 of them). If 2 or 3 categories together are chosen, the MAX and MIN must give the respective resulting month of the combined sales from these categories. The current measure looks like this:
var Status=ALLSELECTED(Status[Status type])
return
calculate(sum(Orders[Amount]);filter((Orders);Orders[Status type] in Status ))
Unfortunately, this measure provides inside a Card the total cumulated sales of the selected categories (for combined categories or individually). By adding this measure into a Matrix, I can see the period breakdown.
However, if I replace SUM by MAX (see code below) I get always the maximum period BUT from one of the 3 categories. For instance, if the user selects the 3 categories together, the measure returns the max sales period from ONLY that category with the "highest max" among the 3 selected categories. This is wrong, because we expect to get the max sales period from the combined selected categories.
calculate(max(Orders[Amount])
Replacing SUM by MIN we get exactly the opposite result. The measure returns the min sales period from ONLY that category with the "lowest min" among the 3 selected categories, and not from the combined categories.
calculate(min(Orders[Amount])
I tried also to use SUMX, MAXX and MINX, the the result is the same.
What am I doing wrong?
Thank you very much in advance,
Gustavo
@Anonymous that worked!! thanks!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |