topic How to count rows of filtered table based on slicer selection in DAX Commands and Tips

How to count rows of filtered table based on slicer selection

char23 — Thu, 01 Aug 2024 15:51:18 GMT

I have the following table below. The months are ordered according to the [Date Rank] column with 1 being the most current month. I have a 1 measure that selects the previous month. So if March is selected, the measure is 3. If Apr is selected, the measure is 2. How can I make a measure to count the number of items/rows in the previous month and place in a card visual. So if Apr is selected as the current month, I want to count the number of rows where Date Rank = 2. And the answer would be 5 since there are 5 rows for march. I also have the following code below, but it looks like my current CALCULATE(COUNTROWS statement does not work because every time I select a month with a filter, the car visual just shows blank. It works when I pass the function a number, but not with the variable. Thank you for any help on solving this.

ID	Status	Date Rank	Month
A	Not Late	3	Jan
B	Not Late	3	Jan
C	Finished	3	Jan
D	Not Late	3	Jan
A	Not Late	2	March
B	Re-opened	2	March
C	Late	2	March
D	Late	2	March
E	Late	2	March
A	Late	1	Apr
B	Late	1	Apr
C	Late	1	Apr

Slicer I have to select month:

First measure I have to select the date rank of the previous month:

Daterankofpreviousmonth =

VAR currentselecteddaterank = SELECTEDVALUE('Table'[Date Rank])

RETURN

IF(

ISBLANK(currentselecteddaterank),

BLANK(),

currentselecteddaterank + 1

)

My current measure to count the number of rows where Table[Date Rank] = daterankofpreviousmonth. But this does not work. The card visual just shows blank.

Countprevious =

VAR currentselecteddaterank = [Daterankofpreviousmonth]

RETURN

IF(

ISBLANK(currentselecteddaterank),

BLANK(),

CALCULATE(

COUNTROWS('Table'),

'Table'[Date Rank] = currentselecteddaterank

)

Re: How to count rows of filtered table based on slicer selection

Anonymous — Fri, 02 Aug 2024 01:26:39 GMT

Hi char23 ,

You can modify your MEASURE.

Countprevious = VAR previousdaterank = [Daterankofpreviousmonth] RETURN IF( ISBLANK(previousdaterank), BLANK(), CALCULATE( COUNTROWS('Table'), ALL('Table'), // Ensure the filter context is removed 'Table'[Date Rank] = previousdaterank ) )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

Re: How to count rows of filtered table based on slicer selection

char23 — Wed, 07 Aug 2024 02:20:45 GMT

This works when looking to count a single value. But when I add it to a visual that has grouping from another column, it shows the same number for each group.

Re: How to count rows of filtered table based on slicer selection

Anonymous — Wed, 07 Aug 2024 06:07:44 GMT

Hi char23 ,

Sorry, could you please describe the problem you are experiencing in more detail and visually with a screenshot or in some other way? This will help us to better assist you in solving the problem.

Best Regards,

Clara Gong

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