Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
mikekim123
Frequent Visitor

DAX measure to remove blank columns

I am using  a measure to filter and show the Actuals based on a user selection from Slicer. 
For eg if the user selects December 2022 he only need to see data for oct 2022,nov 2022,dec2022,FY 2021, Q3 2022,Q2 2022.
I am using a combination of selectedvalue and filter for this measure and it does filters the date table.
Problem is i am getting blank columns for the filtered dates. 
Is it possible for hide the blank columns where the filter is applied?

PFA images and measure i have written:

Measure = 

ACTUALS IN MYEN Visibility =
VAR SelectedMonth =
    SELECTEDVALUE ( 'Date Helper'[FY Monthnum1] )
VAR Selectedyear =
    SELECTEDVALUE ( 'Date Helper'[FY] )
VAR SelectedQuarter =
    SELECTEDVALUE ( 'Date Helper'[FY Quarter] )
VAR Flag =
   
    IF (
        SelectedMonth = "9",
        CALCULATE (
            [ACTUALS IN MYEN],
            FILTER (
                'DATE FORMATTED',
                'DATE FORMATTED'[FY Quarter] = "3"
                   && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "2"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    &&  'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "1"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    &&  'DATE FORMATTED'[FY] = Selectedyear
                    ||  'DATE FORMATTED'[FY] = Selectedyear - 1
                    && 'DATE FORMATTED'[Time Cat] = "FY"
                    ||  'DATE FORMATTED'[FY] = Selectedyear - 2
                    && 'DATE FORMATTED'[Time Cat] = "FY"
            )
        ),
       
         IF (
        SelectedMonth = "8",
        CALCULATE (
            [ACTUALS IN MYEN],
            FILTER (
                'DATE FORMATTED',
                'DATE FORMATTED'[FY Monthnum1] = "8"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Monthnum1] = "7"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "2"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "1"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY] = Selectedyear - 1
                    && 'DATE FORMATTED'[Time Cat] = "FY"
                    || 'DATE FORMATTED'[FY] = Selectedyear - 2
                    && 'DATE FORMATTED'[Time Cat] = "FY"
            )
        ),
   
    IF (
        SelectedMonth = "6",
        CALCULATE (
            [ACTUALS IN MYEN],
            FILTER (
                'DATE FORMATTED',
                'DATE FORMATTED'[FY Quarter] = "2"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "1"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    || 'DATE FORMATTED'[FY Quarter] = "4"
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
                    && 'DATE FORMATTED'[FY] = Selectedyear -1
                    || 'DATE FORMATTED'[FY] = Selectedyear - 1
                    && 'DATE FORMATTED'[Time Cat] = "FY"
                    || 'DATE FORMATTED'[FY] = Selectedyear - 2
                    && 'DATE FORMATTED'[Time Cat] = "FY"
            )
           
        ),8)))

        RETURN
        Flag
mikekim123_0-1676371685417.png

 

1 ACCEPTED SOLUTION

hi @Anonymous ,

I fixed the issue using a change in relationship between date table and Actuals table.
Thanks for the reply

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @mikekim123 ,

Please update the formula of measure [ACTUALS IN MYEN Visibility] as below and check if it can return the expected result...

ACTUALS IN MYEN Visibility =
VAR SelectedMonth =
    SELECTEDVALUE ( 'Date Helper'[FY Monthnum1] )
VAR Selectedyear =
    SELECTEDVALUE ( 'Date Helper'[FY] )
VAR SelectedQuarter =
    SELECTEDVALUE ( 'Date Helper'[FY Quarter] )
VAR Flag =
    CALCULATE (
        [ACTUALS IN MYEN],
        FILTER (
            'DATE FORMATTED',
            (
                VALUE ( 'DATE FORMATTED'[FY Quarter] ) <= VALUE ( SelectedQuarter )
                    && 'DATE FORMATTED'[FY] = Selectedyear
                    && 'DATE FORMATTED'[Time Cat] = "QQ"
            )
                || ( 'DATE FORMATTED'[FY] = Selectedyear - 1
                && 'DATE FORMATTED'[Time Cat] = "FY" )
                || ( 'DATE FORMATTED'[FY] = Selectedyear - 2
                && 'DATE FORMATTED'[Time Cat] = "FY" )
        )
    )
RETURN
    Flag

 

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text format, visual Field settings and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

hi @Anonymous ,

I fixed the issue using a change in relationship between date table and Actuals table.
Thanks for the reply

Anonymous
Not applicable

Hi @mikekim123 ,

It's glad to hear that your problem has been resolved. And thanks for sharing your solution here. Thank you.

Best Regards

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.