Forum Discussion
Advocate IMeasure filter for a table based on 2 slicers
The amount of data that I have in my report and then with that the visuals to then change to make it anonymous data and the time this would take I did a little more research and found using a parameter I could swith between the 2 date options I wanted to filter on and that worked for been able to toggle between the 2 options of which date to filter on. I am still struggling with been able to filter by either of the dates, but for now it should be "good enough"
PLHM - OK this information indicates you are trying to slice with two date columns from the same table. In which case it's no wonder your measures are returning blank. Your date filtering is counteracting each other and reducing the rows to 0.
As well as the parameter I would suggest implementing one or more date tables from which you can remove / change filter context in different situations. This can be achieved via DAX code or a parameter as you say.
If you implement one date table for both columns, make sure the relationships are in-active and you use USERELATIONSHIP inside CALCULATE measures to activate the relationships when necessary.
v-kpoloju-msft - if you are looking to close this one off as solved, I suggest we do so with this response. It is the best we can do in this situation.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Super UserPLHM - Your initial Min and Max varaibles are the issue, because in a measure they are calculating at the row level, rather than over the whole table, you can fix this by removing the row context inside a calculate. Hopefully the code below fixes this, although some sample data would help me test it:
VAR SelectedDateType =
SELECTEDVALUE ( DateSelectionType[Date Type] )
VAR MinSelectedDate =
CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
VAR MaxSelectedDate =
CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates[Date] ) )
RETURN
SWITCH (
SelectedDateType,
"Complete",
VAR CurrentCompletedDate =
MAX ( MergedRS[CompletedDate] ) -- Use MAX (or MIN) to get the value for the current row context
RETURN
IF (
(
NOT ISBLANK ( CurrentCompletedDate )
&& CurrentCompletedDate >= MinSelectedDate
&& CurrentCompletedDate <= MaxSelectedDate
)
|| ISBLANK ( CurrentCompletedDate ),
1,
0
),
"Initiated",
VAR CurrentInitDate =
MAX ( MergedRS[Init Date] ) -- Use MAX (or MIN)
RETURN
IF (
NOT ISBLANK ( CurrentInitDate )
&& CurrentInitDate >= MinSelectedDate
&& CurrentInitDate <= MaxSelectedDate,
1,
0
),
"Complete & Initiated",
VAR CurrentCompletedDate_Combined =
MAX ( MergedRS[CompletedDate] ) -- Use MAX (or MIN)
VAR CurrentInitDate_Combined =
MAX ( MergedRS[Init Date] ) -- Use MAX (or MIN)
RETURN
IF (
(
(
NOT ISBLANK ( CurrentCompletedDate_Combined )
&& CurrentCompletedDate_Combined >= MinSelectedDate
&& CurrentCompletedDate_Combined <= MaxSelectedDate
)
|| ISBLANK ( CurrentCompletedDate_Combined )
)
|| (
NOT ISBLANK ( CurrentInitDate_Combined )
&& CurrentInitDate_Combined >= MinSelectedDate
&& CurrentInitDate_Combined <= MaxSelectedDate
),
1,
0
),
0
)
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
Advocate IHi
Thank you, however the initial MIN and MAX were working just fine, as they are now, they are for the slicer values to be used to filter in the table. The Init Date and CompletedDate are the ones that don't appear to be working / getting a value and still don't get the value for some reason. Any idea why the Init Date and CompletedDate is not been picked up?
- mark_endicott
PLHM - Ok then I misunderstood where you need to remove the row context (this is still the issue). Here's the updated code to fix the issues with Init Date and CompletedDate:
VAR SelectedDateType = SELECTEDVALUE ( DateSelectionType[Date Type] ) VAR MinSelectedDate = MIN ( Dates[Date] ) VAR MaxSelectedDate = MAX ( Dates[Date] ) RETURN SWITCH ( SelectedDateType, "Complete", VAR CurrentCompletedDate = CALCULATE ( MAX ( MergedRS[CompletedDate] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN) to get the value for the current row context RETURN IF ( ( NOT ISBLANK ( CurrentCompletedDate ) && CurrentCompletedDate >= MinSelectedDate && CurrentCompletedDate <= MaxSelectedDate ) || ISBLANK ( CurrentCompletedDate ), 1, 0 ), "Initiated", VAR CurrentInitDate = CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN) RETURN IF ( NOT ISBLANK ( CurrentInitDate ) && CurrentInitDate >= MinSelectedDate && CurrentInitDate <= MaxSelectedDate, 1, 0 ), "Complete & Initiated", VAR CurrentCompletedDate_Combined = CALCULATE ( MAX ( MergedRS[CompletedDate] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN) VAR CurrentInitDate_Combined = CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) -- Use MAX (or MIN) RETURN IF ( ( ( NOT ISBLANK ( CurrentCompletedDate_Combined ) && CurrentCompletedDate_Combined >= MinSelectedDate && CurrentCompletedDate_Combined <= MaxSelectedDate ) || ISBLANK ( CurrentCompletedDate_Combined ) ) || ( NOT ISBLANK ( CurrentInitDate_Combined ) && CurrentInitDate_Combined >= MinSelectedDate && CurrentInitDate_Combined <= MaxSelectedDate ), 1, 0 ), 0 )Again it's really hard to test this without sample data, so if it's still not correct, then you will need to supply that so we can see what is not working.
If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!
- PLHM
I tried this code and unfortuantely it still returns blank. I did some debugging by essentially commenting out the whole switch section and placing only
CALCULATE ( MAX ( MergedRS[Init Date] ), ALLSELECTED ( MergedRS ) ) as the return, and this returns blank, which no dounbt is the problem but why it is returning blank when on that row there is a value I do not understand- mark_endicott
PLHM - Neither will anyone on this forum until you give us some sample data and an idea of the visualisation you are trying to create.
Until you do this your issue cannot be diagnosed.