topic Create count column visual based on column slicer and column group on x-axis in DAX Commands and Tips

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

char23 — Wed, 07 Aug 2024 15:23:39 GMT

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)

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

lbendlin — Thu, 08 Aug 2024 00:36:56 GMT

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.

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

Anonymous — Tue, 13 Aug 2024 07:35:33 GMT

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.