Forum Discussion

StuartSmith's avatar
StuartSmith
Icon for Power Participant rankPower Participant
2 years ago
Solved

Correct Row Count ( Example file attached)

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.

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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. 

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    2 years ago

    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.

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    2 years ago

    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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    StuartSmith
    Icon for Power Participant rankPower Participant
    2 years ago

    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))
  • StuartSmith's avatar
    StuartSmith
    Icon for Power Participant rankPower Participant
    2 years ago

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