Removing only one filter while making calculation in measure not working

[anshul24sharma]

Hello,

If the test data is  like:

USER |     DATE     | THRESH | TYPE
1       |  04/09/22 |    1       |    A

2       |  04/08/22 |    1       |    C

3       |  04/07/22 |    1       |    A 

1       |  04/06/22 |   1.5     |    B

1       |  04/05/22 |   1.5     |    B

1       |  04/04/22 |   0.7     |    B

1       |  04/03/22 |   1.2     |    C

And let's say I have the filter for USER 1  and Date selected is 04/09/22 and Filter pan has filer for TYPE B and THRESH >1.

I want to create a measure which will calculate number of rows for selected USER, TYPE and THRESH, for all the dates less than selected date i.e 04/09/22. The count of rows should be 2 here and these are the 2 rows.

1       |  04/06/22 |   1.5     |    B

1       |  04/05/22 |   1.5     |    B

I am trying something like this:

measure= CALCULATE(COUNTROWS(TABLE),ALL(TABLR[Date])TABLE[Date]< date(2022,04,09))

The idea I am trying is to use ALL or remove filters to remove the Date filter and hence be able to see all the date for rowcount and keep the other filter so that I don't count any unnecessary values. I have tried REMOVE FILTERS  and KEEP FILTERS, but it doesn't work fine. 

It works fine if I put all the values in measure itself, but I don't want to hardcode the measure. This is what hardcoded query looks like:

measure=CALCULATE(COUNTROWS(TABLE),TABLE[Date]< date(2022,04,09),TABLE[THRESH]> 1, TABLE[TYPE] IN {"B"}).

Please let me know if I can create a measure without hardcoding the values.

[Anonymous · Accepted solution]

Hi @anshul24sharma ,

Based on this: I want to create a measure which will calculate number of rows for selected USER, TYPE and THRESH, for all the dates less than selected date

You firstly need a new date table for slicer separately:

Date for slicer = CALENDAR(MIN('Table'[DATE]),MAX('Table'[DATE]))

Then create a flag measure, it could used for visual-level filter later:

Flag = IF( MAX('Table'[DATE]) <= MAX('Date for slicer'[Date]),1,0)

Then calculate the count of rows:

Count = 
var _t= ADDCOLUMNS(ALLSELECTED('Table'),"Flag",[Flag])
return COUNTROWS(FILTER(_t,[Flag]=1))

Final Output:

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

[anshul24sharma]

Hello,

Thank you for reply. But this doesn't work.

If Date value selected in Slicer is 04/09/22, and I use ALLSELECTED, it's not looking at the values where Date< 04/09/22. Becasuse essentially table is filtered for Date 04/09/22 and it cannot find any value less than that date.

Hence I see no results on using this measure.

[amitchandak]

@anshul24sharma , if you want to use selected value in slicer do not use all or use allselected

measure= CALCULATE(COUNTROWS(TABLE),Filter(allselected(TABLR) ,TABLE[Date]< date(2022,04,09))

or

measure= CALCULATE(COUNTROWS(TABLE),Filter(TABLR,TABLE[Date]< date(2022,04,09))