Forum Discussion

char23's avatar
char23
Helper II
2 years ago
Solved

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

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. 

 

IDStatusDate RankPrevious StatusMonthTypeGroup
ANormal3No previous24-MarTask<0
BNormal3No previous24-MarTask1 to 5
CNot Normal3No previous24-MarTask1 to 5
DNot Normal3No Previous24-MarNot Task11 to 15
ANot Normal2Normal24-MayTask6 to 10
BNormal2Normal24-MayNot Task6 to 10 
CNormal2Not Normal24-MayNot Task6 to 10
DNot Normal2Not Normal24-MayTask6 to 10
ANot Normal1Not Normal24-JunTask1 to 5
BNot Normal1Normal24-JunTask<0
DNormal1Normal24-JunTask<0
ENormal1 24-JunTask1 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)
" Slicer"
"DAX help"
"FILTER"
"MEASURE"
Clustered Column Chart
  • lbendlin's avatar
    lbendlin
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
    Anonymous
    Not applicable
    2 years ago

    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.