Solution to turn a hard coded DAX Table OR filter into one which works with a Date Slicer

[SinclairUser]

HI

I’ve got a Power Bi issue relating to a periodic report where I report on the number of cases closed during a reporting period (those with a completion date) AND any which have status of New or Ongoing

I did manage to achieve this with a new filtered Table using DAX:

NewFilteredTable =

FILTER(

    'ICC',

    (

       'ICC' [CMonthY] =  "September2023" || 'ICC' [CMonthY]=""      

    )

)

This filters the main table (‘ICC’) for the reporting period and includes every completed case for “September2023” OR any cases which have yet to be completed (i.e. where the CMonthY is “”.)

The problem is that the criteria (“September2023”) will change every month and I want to find a solution where the user has control and I don’t have to change the DAX code in the Measure to change the report criteria.

I don’t seem to be able to find a solution using Slicers/SELECTEDVALUE but there must be a better way to get the results I’m looking for.

Thanks in advance for any suggestions

[SinclairUser]

Hi 

Thank you for all your suggestons, this has been really bothering me for ages but I have eventually found the solution:  Sorry, for long winded post but based on my difficult experience of trying to find a solution, based on my limited  understanding  rather than anything else, I wanted to provide a comprehensive response.  

Each post above has contributed to my better understandig of the issue, thank youu.

The  main reasons why the following DAX code works:
1. The slicer calendar table 'CAL' has to be unrelated to the main 'ICC' table 

2. It only works as a new Mesaure rather than a new Table.

3. The Measure can then be used for the Visual filter >=1 

'CAL' is a calendar table and 'ICC' is the Main Table - whilst both tables are unrelated they do have a common text field which relate to a date.  MonthY is a text field which has combined the completion month and year (this occurs in Transform Data) - this means it's not formatted as date field.  The equivalent field in 'ICC'   is CMonthY.    This captures any completed cases where the Date Completed occurs within the selected month plus the ongoing, live cases:

New_Filter_Measure =

VAR Q_SELECT = SELECTEDVALUE('Cal'[MonthY])

RETURN

    CALCULATE(

        COUNTROWS('ICC'),

        FILTER(

            'ICC',

            'ICC'[CMONTHY] = Q_SELECT ||  ('ICC'[CMONTHY]="" 

    ))

[SinclairUser]

Hi misx4

Thank you for you reply and I have attempted something like that as a work-around, which filters the data for the previous month (from today) - I just wondered if it was possible to make the report more dynamicso that it would work with a slicer?   Thank you

  ('ICC'[Date Completed] >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1) &&

  'ICC'[Date Completed] <= EOMONTH(TODAY(), -1))

[Anonymous]

Hi @SinclairUser 

You can create a measure  then put it to the related visual filter

e.g 

Measure =
IF (
    SELECTEDVALUE ( 'ICC'[Date Completed] ) IN VALUES ( 'Slicer'[Date] ),
    1,
    0
)

Best Regards!

Yolo Zhu

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

[Anonymous]

If you want it to work with a slicer then you'll either have to take it out of a table, or link the calculated table to your data model. It sounds like you are just after a number, if that is the case you could try the following as a measure:

Completed This Month or New or Ongoing = 

VAR _table = 

FILTER(

    'ICC',

    (

       'ICC' [CMonthY] =  SELECTEDVALUE('ICC' [CMonthY]) || 'ICC' [CMonthY]=""      

    )

)

RETURN

COUNTX(_table, COUNT('YourTable'[ProjectIDField]))

You could also separate this into two measures if you wanted to report on the Completed and ongoing cases separetly, they would look basically the same to the above, you'd just remove one of the filter conditions in each. Hope this helps, let me know if you have any questions.

[mlsx4]

Hi @SinclairUser 

I don't know if it would work but... Have you tried to put as condition TODAY() and play with it.

Like getting the value with something similar to this: FORMAT(TODAY(),"MMMM")&YEAR(TODAY())

NewFilteredTable =
FILTER(

    'ICC',

    (

       'ICC' [CMonthY] =  (FORMAT(TODAY(),"MMMM")&YEAR(TODAY())) || 'ICC' [CMonthY]=""      

    )

)