topic DAX measure to remove blank columns in DAX Commands and Tips

DAX measure to remove blank columns

mikekim123 — Tue, 14 Feb 2023 10:48:19 GMT

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

Re: DAX measure to remove blank columns

Anonymous — Wed, 15 Feb 2023 09:18:56 GMT

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

Re: DAX measure to remove blank columns

mikekim123 — Thu, 16 Feb 2023 16:47:15 GMT

hi Anonymous ,

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

Re: DAX measure to remove blank columns

Anonymous — Fri, 17 Feb 2023 01:12:01 GMT

Hi mikekim123 ,

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

Best Regards