Forum Discussion

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...

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
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.
Anonymous
Not applicable
2 years ago
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.