Forum Discussion

NamNguyen_MMPLM's avatar
NamNguyen_MMPLM
Regular Visitor
10 months ago
Solved

Ignoring Date Slicer with measure

Hi,

I have a summary table in the bottom right. And a lot of slicers on the left. 

 


I want my measure to calculate the MTD and i would like it to be able to be sliced by stone mason, site and permit status BUT NOT the PERM_DATEOFISSUE slicer . The issue is when i move the PERM_DATEOFISSUE slicer (Which is needed for other measures), the MTD only works when i choose today's date (ie. I max out the range in the green box). I thought this code below would allow me to do just that.

MTD TEST = 
VAR StartOfCurrentMonth = 
    CALCULATE(
        MIN(Date_Table[Date]),
        ALL(Date_Table), 
        Date_Table[CurrMonthOffset] = 0
    )

VAR EndOfMTD = TODAY()

VAR vDateBetween = 
DATESBETWEEN(
            Date_Table[Date],
            StartOfCurrentMonth,
            EndOfMTD)

VAR Result = 

CALCULATE(
    COUNT(Permits[PERM_PERMITID]),
    ALL(Date_Table),
    ALL(Permits[PERM_DATEOFISSUE]),
    KEEPFILTERS(vDateBetween),
    USERELATIONSHIP(Date_Table[Date],Permits[PERM_DATEOFISSUE])
)

RETURN Result

 Can anyone assist with my issue

Thanks in advance

Need Help
  • alish_b's avatar
    alish_b
    10 months ago

    Thanks NamNguyen_MMPLM , that makes things clearer. 
    Could you try the following code:

    MTD Permits = 
CALCULATE(
    COUNT(Permits[PERM_PERMITID]),
    REMOVEFILTERS(Date_Table),
    REMOVEFILTERS(Permits[PERM_DATEOFISSUE]),
    REMOVEFILTERS(Permits[PERM_CREATEDDATE]),
    REMOVEFILTERS(Permits[PERM_DATERECEIVED]),
    DATESMTD(
        FILTER(
            ALL(Date_Table[Date]),
            Date_Table[Date] <= TODAY()
        )
    )
)

    So, basically we are attempting to remove any date related filter that comes to this particular measure (you will have to add any other date field  if there is one or comes in the future). Alternatively, we could have added a ALL clause to all of these fields, but give this a try and let us know if it works for you.
    Hope it helps!

8 Replies

  • alish_b's avatar
    alish_b
    Icon for Super User rankSuper User
    10 months ago

    Hi NamNguyen_MMPLM ,

    Can you share some details about how the data model is structured (reationships between date table and permits table) and where these date slicers are coming from (permits table I am assuming or is it the date_table)?

    If you had a model where the date table is linked up with the date of issue (but I assuming it is more complicated than that seeing that you are using USERELATIONSHIP) and you did not want the date slicer to affect things, the query would look something like this:

    MTD Permits = 
CALCULATE(
    COUNT(Permits[PERM_PERMITID]),
    DATESMTD(
        FILTER(
            ALL(Date_Table[Date]),
            Date_Table[Date] <= TODAY()
        )
    )
)

     

    • NamNguyen_MMPLM's avatar
      NamNguyen_MMPLM
      Regular Visitor
      10 months ago

      To be honest, i don't think that line is neccessary. Here is the data model and the active relationship is already to the issue date

      All the date slicers on the left are using columns from the Permits table (ie.The date slicer is using PERMITS[PERM_DATEOFSSUE])

       

      • alish_b's avatar
        alish_b
        Icon for Super User rankSuper User
        10 months ago

        Thanks NamNguyen_MMPLM , that makes things clearer. 
        Could you try the following code:

        MTD Permits = 
CALCULATE(
    COUNT(Permits[PERM_PERMITID]),
    REMOVEFILTERS(Date_Table),
    REMOVEFILTERS(Permits[PERM_DATEOFISSUE]),
    REMOVEFILTERS(Permits[PERM_CREATEDDATE]),
    REMOVEFILTERS(Permits[PERM_DATERECEIVED]),
    DATESMTD(
        FILTER(
            ALL(Date_Table[Date]),
            Date_Table[Date] <= TODAY()
        )
    )
)

        So, basically we are attempting to remove any date related filter that comes to this particular measure (you will have to add any other date field  if there is one or comes in the future). Alternatively, we could have added a ALL clause to all of these fields, but give this a try and let us know if it works for you.
        Hope it helps!

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    10 months ago

     

    • Select the PERM_DATEOFISSUE slicer.

    • Go to the Format tab in the ribbon.

    • Click Edit interactions.

    • On the visual where you want to ignore the slicer filter, click None (the icon in the upper right corner of that visual).

     

     

  • NamNguyen_MMPLM's avatar
    NamNguyen_MMPLM
    Regular Visitor
    10 months ago

    I have included a picture of the numbers when i change the date of issue slicer and how the value changes

    Reason i cant block the interaction completely is because i still want the # Total Permits to change for instance. But i want MTD to be the same more or less (unless sliced by Status or company)