Measure with countrows and earlier filter

[Anonymous]

Hey All,
I have a calculated column which I want to migrate to a measure.

I want to use this measure in a few different visuals and apply different filters on those visuals.

Mumber of Reviews Per Year =
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        Table1[id]
            = EARLIER ( Table1[id] )
    ),
    FILTER (
        Table1,
        Table1[REVIEW_DATE].[Year]
            = EARLIER ( Table1[REVIEW_DATE].[Year] )
    )
)

How can I do it?
The Measure does not like the earlier function.

Thanks!
A

[Ashish_Mathur]

Hi,

Ensure you have a Calendar Table with a Year column.  Build a relationship from the Review Date column of Table1 to the Date column of the Calendar Table.  To your visual, drag Year from the Calendar Table and id from Table1.  Write this measure

Mumber of Reviews Per Year = COUNTROWS(Table1)

Hope this helps.

[Anonymous]

@Ashish_Mathur 

Thanks for your reply.

I followed your steps but it does not give the required result.

1. The year column is empty, not sure why.

2. The count gives 1 for each record.

M = 
COUNTROWS('Table1')

Thanks!
A

[Ashish_Mathur]

It should work.  I'll need to see your file.

[Anonymous]

Hi @Ashish_Mathur 

I understand where you are coming from.

Yet, the file contains sensetive data which I cannot share.

If you other sugeestions, I'll be happy to hear.

Thanks!
A

[Zubair_Muhammad]

@Anonymous 

Try replacing EARLIER with SELECTEDVALUE

[Anonymous]

Hi @Zubair_Muhammad 

I am getting a count of 1 for each ID, which is wrong.

Thnaks
A

[Zubair_Muhammad]

@Anonymous 

You might also need to replace Table1 with ALL(Table1) or Allselected(Table1),

because in a MEASURE you have filter context

[Anonymous]

Hi @Zubair_Muhammad 

Thanks for the reply.

When I use ALL() I get the total count of the table, seeme like the filters below are ignored.

    FILTER (
        Table1,
        Table1[id]
            = SELECTEDVALUE( Table1[id] )
    ),
    FILTER (
        Table1,
        Table1[REVIEW_DATE].[Year]
            = SELECTEDVALUE( Table1[REVIEW_DATE].[Year] )
    )

When Using ALLSELECTED I am getting a count of '1' again.

Appreciate any further advise on this, if this is possible at all.

Cheers!
A

[thompsonday]

Hi @Anonymous ,

Did you finally get the solution to this?