Create count column visual based on column slicer and column group on x-axis

[char23]

I am trying to write a measure to create a dynamic visual. I have the following table below as a single table in my file. I want the user to be able to select a month. If the user selects June. Then I want previous month to be used in the measure (This would be May since this is the since 1 + the date rank gives the group of the previous month). Then I want the measure to count the number of rows where multiple filter conditions are met. I want to count rows where Dank rank =2, Status and Previous Status = "Not Normal" and where Type = "Task". So this would return a 1 because there is only one row where all conditions are met. But I want the measure to update based on what the user selects as the current month. If the user does not select a month, then I want to show blank for the measure. Right now, I am able to get the correct count, but when I group the visual by the "Group" column on the x-axis, I get the same number for each group rather than just the count for that specific group. (Table and current dax formula I have is below). Thank you for any help on this. 

ID	Status	Date Rank	Previous Status	Month	Type	Group
A	Normal	3	No previous	24-Mar	Task	<0
B	Normal	3	No previous	24-Mar	Task	1 to 5
C	Not Normal	3	No previous	24-Mar	Task	1 to 5
D	Not Normal	3	No Previous	24-Mar	Not Task	11 to 15
A	Not Normal	2	Normal	24-May	Task	6 to 10
B	Normal	2	Normal	24-May	Not Task	6 to 10
C	Normal	2	Not Normal	24-May	Not Task	6 to 10
D	Not Normal	2	Not Normal	24-May	Task	6 to 10
A	Not Normal	1	Not Normal	24-Jun	Task	1 to 5
B	Not Normal	1	Normal	24-Jun	Task	<0
D	Normal	1	Normal	24-Jun	Task	<0
E	Normal	1		24-Jun	Task	1 to 5

Measure =

VAR SelectedMonthRank =

    CALCULATE(

        MAX('Table'[Date Rank]),

        ALLSELECTED('Table'[Month]),

        ALLSELECTED('Table'[Group]),          

        'Table'[Month] = SELECTEDVALUE('Table'[Month])

    )

VAR PreviousMonthDateRank = SelectedMonthRank + 1

VAR _tablecalculation =

    CALCULATETABLE(

        'Table',

        FILTER(

            ALL('Table'),

            'Table'[Date Rank] = PreviousMonthDateRank

            && 'Table'[Status] = "Not Normal"

            && 'Table'[Previous Status] = "Not Normal"

            && 'Table'[Type] = "Task"

    )

    )

RETURN

COUNTROWS(_tablecalculation)

[lbendlin · Accepted solution]

You cannot do all this with only one table.  You need to create a disconnected table for the month slicer, and then use a measure as the filter for your original table visual.

[Anonymous · Accepted solution]

Hi @char23 

Thank you very much lbendlin for your prompt reply. The solution you offer is reasonable. Let me add some details here.

Create a new month table.

Date = 
DISTINCT(
    SELECTCOLUMNS(
        'Table',
        "Month",
        'Table'[Month].[Month]
    )
)

Create a measure.

Measure = 
var _selectMonth = SELECTEDVALUE('Date'[Month])
var _DateRank = 
CALCULATE(
    SELECTEDVALUE('Table'[Date Rank]),
    FILTER(
        ALL('Table'),
        'Table'[Month].[Month] = _selectMonth
    )
) + 1
var _tablecalculation = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[Date Rank] = _DateRank
        && 
        'Table'[Status] = "Not Normal"
        && 
        'Table'[Previous Status] = "Not Normal"
        && 
        'Table'[Type] = "Task"
    )
)
RETURN _tablecalculation

Here is the result.

Regards,

Nono Chen

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

[lbendlin · Accepted solution]

You cannot do all this with only one table.  You need to create a disconnected table for the month slicer, and then use a measure as the filter for your original table visual.