Dynamic number frequency column calculation based on date range slicer

[Anonymous]

I need help with calculating a dynamic column.

I want to calculate the frequency of a number within a date range selected by a slicer.

I'm currently using this to calculate the column and it works fine, but it calculates for the whole table. But it's not accurate because it should be calculating the frequency for the selected date range, not the entire table.

frequency = 
COUNTX (
    FILTER ( Table2, EARLIER ( Table2[number] ) = Table2[number] ),
    Table2[number]
)

I have a slicer on the report for the date that charts the number frequency over time in the selected date range. The chart's not accurate because it's showing the frequency of the number in for all time, not the range selected.

I've been trying to figure out how to use FILTER and ALLSELECTED in the DAX formula, but I can't get it to work right and I'm not even sure if they're the right thing to use.

How can I change the "frequency" formula so that it calculates the frequency of "days" based on the date range selected by the slicer?

Here's example data for reference:

day	number	frequency
1/1/22	78
1/2/22	2
1/2/22	78
1/5/22	1
1/8/22	2

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a measure. Both the following measures can work.

frequency = COUNTROWS(FILTER(ALLSELECTED('Table'),[number]=MAX('Table'[number])))

frequency2 = CALCULATE(COUNT('Table'[day]),FILTER(ALLSELECTED('Table'),[number]=MAX('Table'[number])))

Best Regards,

Stephen Tao

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

[Jos_Woolley]

Hi,

Measures are often far simpler and more effective than Calculated Columns, not least since they respond to filtering.

MyFrequency :=
COUNTROWS( Table2 )

which can then be used in a visual alongside the activity_days field.

Regards

[Anonymous]

Thanks for the quick reply @Jos_Woolley 

Unfortunately, I don't think that will work in my case because the frequency value is then charted over time so that we see trends of "day_frequency" for the date range selected in the slicer.