topic Matrix full of measures - when formatting blank rows show up! in DAX Commands and Tips

Matrix full of measures - when formatting blank rows show up!

Anonymous — Thu, 01 Oct 2020 00:22:30 GMT

Hi everyone,

I have generated a matrix full of measures a bit like this

and the result is brilliant, showing something like this:

However, i'm trying to format the values (e.g. Area to 1 decimal place, Tonnes rounded to nearest number etc)

and so i attempted a format function:

and suddenly all these blank rows show up with no data (but is all the available dates in my date table which is one of the columns)

Does anyone know (1) is there another way to format my numbers in the matrix full of measures and / or why the table suddenly show so many empty rows?

Re: Matrix full of measures - when formatting blank rows show up!

amitchandak — Thu, 01 Oct 2020 01:24:35 GMT

Anonymous , Difficult to tell without looking at data.

The formatting it changing it into text. That might have cause this issue. There was the solution proposed by Guyinacube using external tools. See if that can help

https://www.youtube.com/watch?v=vlnx7QUVYME

measure slicer
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

Re: Matrix full of measures - when formatting blank rows show up!

Anonymous — Thu, 01 Oct 2020 21:50:13 GMT

thanks amitchandak , i had a look at the youtube and it looks good (although i'm still struggling to wrap my head around it a bit, as the values are not showing as i want).

i tidied up my example and hope this works for a download:

https://drive.google.com/file/d/13WUUB3H8jkjeApp5P-kmnr_srXeJSx7B/view?usp=sharing

the top table has all the data i want,

but i want to format it to the table below (i have compiled all the measures into a matrix)so i can have 2 layer headers

however, i want to be able to format the columns differently

e.g. area to 1 decimal place,

volume tonnes to nearest digit, but t/ha to 1 decimal place, sales to $ etc etc.

Would really appreciate any feed back. Been going through google searches for ages now and can't come up with much more!

Cheers,

Re: Matrix full of measures - when formatting blank rows show up!

Anonymous — Fri, 17 Sep 2021 17:32:45 GMT

I ran into the same issue, using the LEN function to return blanks in case of zero characters worked for me:

VAR Result = .... (your measure that includes formatting)

RETURN

IF(LEN(Result)=0, BLANK(), Result)

Re: Matrix full of measures - when formatting blank rows show up!

Anonymous — Sat, 25 Sep 2021 18:33:04 GMT

Hello,

i formatted already the first 6 columns, I let you do the rest 😉

Measure =

SWITCH( TRUE(),

MAX(MYOB_Group[Measure Name])="Area", IF(sum(HarvestSummary[Area (ha)]) <> BLANK(), FORMAT(sum(HarvestSummary[Area (ha)]), "0.0"), BLANK() ),

MAX(MYOB_Group[Measure Name])="Volume",IF(sum(FLITS[Nett]) <> BLANK(), FORMAT(sum(FLITS[Nett]), "0"), BLANK() ),

MAX(MYOB_Group[Measure Name]) = "Volume (t/ha)", IF([Vol_tperha] <> BLANK(), FORMAT([Vol_tperha], "0"), BLANK()),

MAX(MYOB_Group[Measure Name]) = "Log Sales", IF([Calc_LogSales] <> BLANK(), FORMAT([Calc_LogSales] * -1, "($ 0)"), BLANK()),

MAX(MYOB_Group[Measure Name]) = "Log Sales ($/t)", IF([Calc_LogSales_per_ton] <> BLANK(), FORMAT([Calc_LogSales_per_ton] *-1, "($ 0.00)"), BLANK()),

MAX(MYOB_Group[Measure Name]) = "Logging", IF([Calc_Logging] <> BLANK(), FORMAT([Calc_Logging], "$ 0"), BLANK()),

MAX(MYOB_Group[Measure Name]) = "Logging ($/t)", [Calc_Logging_per_ton],

MAX(MYOB_Group[Measure Name]) = "Cartage", [Calc_Cartage],

MAX(MYOB_Group[Measure Name]) = "Cartage ($/t)", [Calc_Cartage_per_ton],

MAX(MYOB_Group[Measure Name]) = "Management ($)", [Calc_Mgt_Total],

MAX(MYOB_Group[Measure Name]) = "Management ($/t)", [Calc_Mgt_per_ton],

MAX(MYOB_Group[Measure Name]) = "Gross Stumpage", [GrossStumpage],

MAX(MYOB_Group[Measure Name]) = "Gross Stumpage ($/t)", [GrossStumpage_per_ton],

MAX(MYOB_Group[Measure Name]) = "Gross Stumpage ($/ha)", [GrossStumpage_per_ha],

MAX(MYOB_Group[Measure Name]) = "Roads & Landings", [Calc_Roads_Total],

MAX(MYOB_Group[Measure Name]) = "R&L ($/t)", [Calc_Roads_per_ton],

MAX(MYOB_Group[Measure Name]) = "R&L ($/ha)", [Calc_Roads_per_hac],

MAX(MYOB_Group[Measure Name]) = "Other", [Calc_Other],

MAX(MYOB_Group[Measure Name]) = "Other ($/t)", [Calc_Other_per_ton],

MAX(MYOB_Group[Measure Name]) = "Net From Harvest", [Calc_Net],

MAX(MYOB_Group[Measure Name]) = "Net From Harvest ($/t)", [Calc_Net_per_ton],

MAX(MYOB_Group[Measure Name]) = "Net From Harvest ($/ha)", [Calc_Net_per_ha],

BLANK()

)

If this solves your problem, please mark it as the solution so others can find it.

Regards