Correct Row Count ( Example file attached)

[StuartSmith]

I have a table visual that is made up of various columns (measures) and there is also a visual filter...

Closed before =

Sumx(values('Master Lookup Table'[Buidling Code]),

IF([Location Status] = "Archived" && [Date Closed] < [1) First Period Date] , 1,

0))

The above visual filter, filters the table rows depending on if the "Loction Status" is "Archived" and the "Date Closed" is before the "First Period Date" which is a Slicer filtering a date table.  I now need to be able to count the rows of the table visual.  I can do "Countrows", which does count the rows, but doesnt take into account the above Visual Filter when a date is selected.

CountAttritionRows.pbix 

How can it get a correct row count figure? Hope that makes sense.

Thanks in advance.

[Anonymous · Accepted solution]

Hi @StuartSmith ,

Please update your measure.

Count Rows = 
VAR AllRows = COUNTROWS(FILTER(ALLSELECTED('Master Lookup Table'),[Closed before]=0))
Return
AllRows

Best Regards,

Neeko Tang

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

[StuartSmith]

Thanks guys, I will take a look, although not familiar with ADDcOLUMN & SUMMERISE, so will take a look.  

[HotChilli]

Hi @StuartSmith ,

referring to your original question: To count the rows in the visual you will have to create a measure - using ADDCOLUMNS(SUMMARIZE(   - to create a virtual tablewhich is the same as the visual table and then count the rows of that table.

All the same fields from the visual will have to be included in the virtual table and then the correct filtering applied to mimic the table visual.

I advise testing with a simplified version of your table to start with, for example, the fields from the visual plus one measure, and if you can get that to work, build it up.

[StuartSmith]

Unsure how I can change the report so it filters "Archvied" sites that are closed before the date slicer selection without using...

Closed before =

Sumx(values('Master Lookup Table'[Buidling Code]),

IF([Location Status] = "Archived" && [Date Closed] < [1) First Period Date] , 1,

0))

[Bibiano_Geraldo]

Hi, i was checking your project, you need to remodel it, you are changing the filter but you're still seing the same thing.
But now asking your question, your problem its because the 'Master Lookup table' its not related with 'Date Table',  so to be able to filter values using date your need to relate these tables mentioned above.
Look the experiment bellow, when you click into matrix the controws its working normally.