Not all Data showing up in bar graph or tables

[biancabee12]

Hi All,

I have the a column formula called RiskCategory which  shows the 3 risk types, extreme, medium and high. Because these ratings are in the rows of the columns i had to use the below formula so it puts the correct data under the correct quarter. To make sure it goes to correct data i have done a switch called quarter labels which goes in the axis and then the riskcategory in the legend and yaxis. What i am having issues with is that the Medium category is not showing for Q4 2023, Q1 2024 and Q2 2024 but is showing fine for Q3 2023. ANy help will be much appreciated

Quarter_Labels =

    SWITCH(TRUE(),

        NOT(ISBLANK('Risk Trend'[Q3 2023])), "Q3 2023",

        NOT(ISBLANK('Risk Trend'[Q4 2023])), "Q4 2023",

        NOT(ISBLANK('Risk Trend'[Q1 2024])), "Q1 2024",

        NOT(ISBLANK('Risk Trend'[Q2 2024])), "Q2 2024",

        BLANK()

    )

RiskCategory =

IF(

    AND('Risk Trend'[Q3 2023] >= 15, 'Risk Trend'[Q3 2023] <= 25),

    "Q3 2023 Extreme",

        IF(

        AND('Risk Trend'[Q3 2023] >= 4, 'Risk Trend'[Q3 2023] <= 6),

        "Q3 2023 Medium",

        IF(

            AND('Risk Trend'[Q3 2023] >= 8, 'Risk Trend'[Q3 2023] <= 12),

            "Q3 2023 High",

            IF(

                AND('Risk Trend'[Q4 2023] >= 15, 'Risk Trend'[Q4 2023] <= 25),

                "Q4 2023 Extreme",

                IF(

                    AND('Risk Trend'[Q4 2023] >= 4, 'Risk Trend'[Q4 2023] <= 6),

                    "Q4 2023 Medium",

                    IF(

                        AND('Risk Trend'[Q4 2023] >= 8, 'Risk Trend'[Q4 2023] <= 12),

                        "Q4 2023 High",

                        IF(

                            AND('Risk Trend'[Q1 2024] >= 15, 'Risk Trend'[Q1 2024] <= 25),

                            "Q1 2024 Extreme",

                            IF(

                                AND('Risk Trend'[Q1 2024] >= 4, 'Risk Trend'[Q1 2024] <= 6),

                                "Q1 2024 Medium",    

                                IF(

                                    AND('Risk Trend'[Q1 2024] >= 8, 'Risk Trend'[Q1 2024] <= 12),

                                    "Q1 2024 High",

                                    IF(

                                        AND('Risk Trend'[Q2 2024] >= 15, 'Risk Trend'[Q2 2024] <= 25),

                                        "Q2 2024 Extreme",

                                        IF(

                                            AND('Risk Trend'[Q2 2024] >= 4, 'Risk Trend'[Q2 2024] <= 6),

                                            "Q2 2024 Medium",

                                            IF(

                                                AND('Risk Trend'[Q2 2024] >= 8, 'Risk Trend'[Q2 2024] <= 12),

                                                "Q2 2024 High",

                                                BLANK()

                                            )

                                        )

                                    )

                                )

                            )

                        )

                    )

                )

            )

    )

)

)

[Anonymous]

Hi @biancabee12 ,

Ensure that the data for Q4 2023, Q1 2024, and Q2 2024 within the 'Risk Trend' column actually contains values that fall within the range for the "Medium" category (i.e., >= 4 and <= 6). It's possible that there might be no data points within this range for these quarters, which would result in the "Medium" category not being displayed.

Consider simplifying your DAX formula to make it easier to debug and maintain

RiskCategory = 
SWITCH(
    TRUE(),
    AND('Risk Trend'[SelectedQuarter] >= 15, 'Risk Trend'[SelectedQuarter] <= 25), "Extreme",
    AND('Risk Trend'[SelectedQuarter] >= 4, 'Risk Trend'[SelectedQuarter] <= 6), "Medium",
    AND('Risk Trend'[SelectedQuarter] >= 8, 'Risk Trend'[SelectedQuarter] <= 12), "High",
    BLANK()
)

Best Regards,

Xianda Tang

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

[biancabee12]

Hi @Anonymous 

I can confirm that there is data within these columns. Q3 2023 shows the data for Medium but not for the other quarters when there is data for it. When i select edit query, the data is definitely there but the option is not appearing in the filter. i even created another bar graph with only Q4 2023 and then it appeared up. I added Q3 2023 and the data didnt appear up.  Tried the formula you've given and still the same issue

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result very clearly.