Create a measure for a stacked column chart that suppress counts based on two conditions

[claireberry]

Hi, I have a "population flag" table with a Category column with values of race(where Grouping = "race") and a Flag column with the values of either flag or blank. I have a "spinoff" table with race_c and count column( the count column has value of 1 for each row).  I want to create a measure where if race_c's matching counterpart in the Category column for a state( state code for "population flag" table, state for "spinoff" table), has a corresponding "flag" value, and if the count for the "spinoff" table for the state and race_c combination is less than 11, then return blank for the entire stacked column visual when state is in the x-axis, and this new measure in the y-axis, race_c as legend.

In other words, create a measure that looks at all races for the current state that are flagged (from the Population flag table, where Grouping = "race" and Flag = "Flag") and checks if any of those flagged races has a total count (from the Spinoff table) less than 11. If at least one flagged race meets that condition, the measure returns BLANK for every race in that state (thus hiding the entire state’s bar). Otherwise, it returns the current race’s count.

I have a prelimary code, however, this code created a measure that will suppress only the specific bar segment of the race that met the conditions, but I want the entire bar for that state to not show in a stacked column chart, if there is one race in a state that met the two conditions. 

Filtered_Race_Count =

VAR CurrentState = SELECTEDVALUE(Spinoff[state])

VAR _flagCheck =

    LOOKUPVALUE(

    'Population flag'[Flag],

    'Population flag'[State code], VALUE( SELECTEDVALUE(Spinoff[state]) ),

    'Population flag'[Category], SELECTEDVALUE(Spinoff[race_c])

)

VAR _spinoffCount =

    CALCULATE(

        SUM(Spinoff[count]),

        KEEPFILTERS(

            FILTER(

                Spinoff,

                Spinoff[state] = SELECTEDVALUE(Spinoff[state]) &&

                Spinoff[race_c]     = SELECTEDVALUE(Spinoff[race_c])

            )

        )

    )  

VAR CurrentRaceCount =

    CALCULATE(

        SUM(Spinoff[count]),

        Spinoff[state] = CurrentState,

        Spinoff[race_c] = SELECTEDVALUE(Spinoff[race_c])

    )RETURN

IF(

    // Hide this category if flagged and if total count ≤ 10

    _flagCheck = "Flag"

    && _spinoffCount <= 10,

    BLANK(),            // Return no value (hides the bar segment)

    _spinoffCount       // Otherwise, return the sum

)

population flags table:

State	Category	Grouping	Population	State Code	flag
Montana	American Indian or Alaska Native	Race	78473	30	Flag
Montana	Asian or Pacific Islander	Race	13220	30	Flag
Montana	Black or African American	Race	11185	30	Flag
South Dakota	American Indian or Alaska Native	Race	82007	46	Flag
South Dakota	Asian or Pacific Islander	Race	17099	46	Flag
South Dakota	Black or African American	Race	26431	46	Flag
Utah	American Indian or Alaska Native	Race	57378	49	Flag
Utah	Black or African American	Race	63705	49	Flag
Montana	White	Race	898988	30
Utah	White	Race	7879999	49

 spinoff table

state name	race_c	state	count
Montana	American Indian or Alaska Native	30	1
Utah	Black or African American	49	1
Utah	Black or African American	49	1
Utah	Black or African American	49	1
Utah	Asian or Pacific Islander	49	1
South Dakota	White	46	1
South Dakota	White	46	1
South Dakota	White	46	1
South Dakota	White	46	1
Montana	White	30	1
Montana	White	30	1
Montana	White	30	1
Montana	White	30	1
Montana	White	30	1
Montana	White	30	1
South Dakota	White	46	1
South Dakota	White	46	1
South Dakota	White	46	1
South Dakota	White	46	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Utah	White	49	1
Montana	Other or multi-race	30	1
Utah	Other or multi-race	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1
Utah	Race not stated	49	1

At the end, I want it to look like this below, with no bar for Montana and Utah showing. ( don't worry about the number in the labels not match)

[v-ssriganesh]

Hi @claireberry,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @claireberry,
Could you please confirm if your query have been resolved by the solution provided by? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @claireberry ,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Anonymous · Accepted solution]

Hi @claireberry,

Could you please share a bit more about your scenario?

Please try to modify your DAX as:

Filtered_Race_Count =
VAR CurrentState = SELECTEDVALUE(Spinoff[state])
VAR FlaggedRaces =
    CALCULATETABLE(
        VALUES('Population flag'[Category]),
        'Population flag'[Grouping] = "race",
        'Population flag'[Flag] = "Flag",
        'Population flag'[State Code] = CurrentState
    )
VAR Check =
    COUNTROWS(
        FILTER(
            FlaggedRaces,
            VAR RaceCategory = 'Population flag'[Category]
            VAR RaceCount =
                CALCULATE(
                    SUM(Spinoff[count]),
                    ALL(Spinoff[race_c]),
                    Spinoff[state] = CurrentState,
                    Spinoff[race_c] = RaceCategory
                )
            RETURN RaceCount <= 10
        )
    ) > 0
RETURN
IF(Check, BLANK(), SUM(Spinoff[count]))