Count rows before range set in date slicer

[camc00]

Hi all,

I am using a date 'Between' slicer to control many visuals in my report, it is using Calendar[Date].
In one of my table visuals I would like to create a column that includes a count of all rows in my 'Opportunity' table where the 'SubmissionDate' is less that the start date of my slicer, irrespective of whether any other date within the row falls within the date range set.

If any other information is required to resolve this request please let me know.

Thanks

[v-xinruzhu-msft · Accepted solution]

Hi @camc00 

You can use crossfilter() function, you can refer to the following sample

Sample data 

Date table

Relationship

Then create a measure

Measure = CALCULATE(COUNTROWS('Table'),'Table'[Column2]<MIN('Table 2'[Date]),CROSSFILTER('Table 2'[Date],'Table'[Column2],None))

Output

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.

[ryan_mayu]

maybe you can try use ALL function to disable the slicer

ALL function (DAX) - DAX | Microsoft Learn

or you can use visual interact to disable the relationship between the visual and the slicer

Change how visuals interact in a report - Power BI | Microsoft Learn

[camc00]

Thanks for your reply, the ALL function looks good, however I would still need to use the start date of the slicer to be able to filter all rows where SubmissionDate is less is than that start date.

How would I manage this?

Thanks

[ryan_mayu]

could you pls provide some sample data and the expected output?