Hiding blank rows & Columns

[Anonymous]

Hello All,

I would be grateful if someone can let me know a way to hide blank rows and columns in a matrix visualization of Power BI.

Service-line and country are columns from a table but the values are Measures.

Service line	USA	Canada	Mexico	UK	France
Finance	1	1		1	1
Accounting
Development	1	1		3	3
Marketing	1	1		1	1

Serviceline is present in a table called "Value" were as Country name is present in a table called "Country". 

I tried hiding the column by selecting "Wrap word" on and then hiding Mexico but I would like to know if there is a query to hide blank rows or columns.

Thanks,

ShriHanuman.

[v-frfei-msft]

Hi @Anonymous ,

What is your excepted output?

[Anonymous]

Thank you for asking.

Every month or year the data keeps changing and there can be a value in the Service line say "Accounting"  this month but next month this may be blank. Similarly, the location say "Mexico" may have value or may not have value. So, I would like to know a way to filters/ hide/not show blank rows and columns. 

Accounting and Mexico are just for example and I cannot use hardcoded formulas that refers to just one country or service line :

example: 

Measure = SUMX('Value', IF(RELATED(Country[Country]) = "France", BLANK(), 'Value'[Value]))

Let me know if this helps. Sorry for the confusion. I just started using Power BI.

Regards

[camargos88]

@Anonymous ,

If the column/row is entirely blank, the visual will hide it by default.

Ricardo

[v-frfei-msft · Accepted solution]

Hi @camargos88 ,

[Greg_Deckler]

No automatic way to hid columns that I know of.

[Anonymous]

Is there a query or any other way to hide blank rows or columns?

[camargos88]

Hi @Anonymous ,

I have a situation like that:

Which operation are you doing ?

Measure = SUMX('Table (2)'; IF('Table (2)'[Country] = "Mexico";BLANK(); 'Table (2)'[Value]))

Ricardo

[Anonymous]

I wrote the below measure:

BlankRows = filter('values','values'[Service Line] = BLANK()) but this gave me below error:

Function RELATED expects a fully qualified column reference as its argument

Values is the table and Service line is a column within the table.

[camargos88]

@Anonymous ,

Can you provide some data sample ?

Ricardo

[Anonymous]

---

@camargos88 wrote:

@Anonymous ,

Can you provide some data sample ?

Ricardo

---

Please refer the below tables for the structure of the data set:

Value Table

Service line	Ops Geo	Value
Accounting	NY	1
Finance	Toronto	1
Development	London	1
Marketing	Paris	1
Accounting	London	1
Finance	Paris	1
Development	NY	1
Marketing	Toronto	1
Accounting	London	1
Finance	NY	1
Development	Toronto	1
Marketing	Paris	1

country table:

Ops Geo	Country
NY	USA
Toronto	Canada
London	UK
Paris	France

[camargos88]

Hi @Anonymous ,

I created this file as as example: Download PBIX 

Ricardo