Forum Discussion

SinclairUser's avatar
SinclairUser
Regular Visitor
2 years ago

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

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 O...
Need Help
SinclairUser's avatar
SinclairUser
Regular Visitor
2 years ago

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's avatar
Anonymous
Not applicable
2 years ago

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.