How to include a secondary filter within this measure?

[jwillis07]

Hi all, hoping you can help me with something...

Say I have Table1, with the below columns:

• id
• date
• type

I've written this measure, and by written, I mean copied off the internet somewhere and can't remember where:

cumulative = 
CALCULATE(
	COUNTA(Table1[id]),
	FILTER(
		ALLSELECTED(Table1[date]),
		ISONORAFTER(Table1[date], MAX(Table1[date]), DESC)
	)
)

Which then produces this visual by adding the date column to the x-axis, and the measure as a line value:

So my question is: How can I incorporate a second filter where the column "type" is "A"?

The aim is to replace this singular line with 4, one for each type, "A", "B", "C" & "D"

Thank you in advance!

[Samarth_18]

Hi @jwillis07 ,

Please try this:-

cumulative = 
CALCULATE(
	COUNTA(Table1[id]),
	FILTER(
		ALLSELECTED(Table1[date]),
		ISONORAFTER(Table1[date], MAX(Table1[date]), DESC) && 
                Table1[type] in {"A","B","C","D"}
	)
)

Thanks,

Samarth

[jwillis07]

Hi, thanks for the response. Tried this but it doesn't seem to work unfortunately as it won't allow me to add Table1[type] - the type part is greyed out.

I think maybe it's something to do with the ALLSELECTED part.

Whenever I type... Table1[ ... The autofill options only show me the table, the date column and a different measure in this table - won't allow me to enter any other column names.

[mh2587]

You can simply use page filter by selecting type A,B, or C 

[jwillis07]

Hi, thanks for the response. Yeah you're right in that I could just apply add 'type' to the page filter but that wouldn't allow for me to have 4 lines in one visual, one for each type.

[mh2587 · Accepted solution]

cumulative =

CALCULATE(

    COUNTA(Table1[id]),

    FILTER(

        ALLSELECTED(Table1[date]),

        ISONORAFTER(Table1[date], MAX(Table1[date]), DESC)

    ),Table[Type] == "A"

)

Just Change A for the other three measure then put all the measures

[jwillis07]

Yep, this is it. Thank you very much, really appreciate it.