topic hiding empty columns in the matrix based on measures in DAX Commands and Tips

hiding empty columns in the matrix based on measures

emillia1991 — Thu, 01 Sep 2022 12:13:34 GMT

Dear communty,

I'm new here and using Power BI since few months.

I wanted to present sales and sales difference in two different points in time. I want to choose points in time in slicer. My problem is that I have empty columns:

I created D_sales by applying measures, so that I can choose two years and it will calculate the percentage difference for these two years. Below the measures I used:

D_sales = 100*(DIVIDE(

[Diff_sales],

CALCULATE(

SUM('Sheet1'[sales]),

FILTER('Sheet1',Sheet1[year]=MIN(

'Sheet1'[year]))

)

))

Diff_sales = IF(

HASONEVALUE('Sheet1'[year]),

BLANK (),

CALCUlATE(

SUM(Sheet1[sales]),

FILTER('Sheet1','Sheet1'[year]=MAX(Sheet1[year]

))

)

- CALCULATE(

SUM(Sheet1[sales]),

FILTER('Sheet1','Sheet1'[year]=MIN(Sheet1[year]

))

)

Hiding these empty columns by dragging is not possible because I want to update data and choose different years in the filter. I'm searching the Internet since yesterday. However, I'm not very succesful. I was wondering if it is possible to write sth in the code so that it won't display empty columns.

Re: hiding empty columns in the matrix based on measures

amitchandak — Thu, 01 Sep 2022 15:22:13 GMT

emillia1991 , Try like

Diff_sales =
var _max = maxx(allselected(Sheet) ,Sheet1[year])
var _min = Minx(allselected(Sheet) ,Sheet1[year])
return
IF(
HASONEVALUE('Sheet1'[year]),
BLANK (),
CALCUlATE(
SUM(Sheet1[sales]),
FILTER('Sheet1','Sheet1'[year]=_max
))
- CALCULATE(
SUM(Sheet1[sales]),
FILTER('Sheet1','Sheet1'[year]=_min
))
)

Re: hiding empty columns in the matrix based on measures

emillia1991 — Fri, 02 Sep 2022 09:26:11 GMT

Thank you amitchandak for your reply. Unfortunately it doesn't work. I still see the empty columns 😞