Hide rows with no values from template P&L

DAX DAX Calculations
1

Hi,
Based on one of the EDNA courses, I have made a profit and loss account with a template. Everything is working fine except one thing. For some customers, not all topics are applicable.
So if a customers does not have for example Exploitatiekosten, it should not be shown.
Is there a way to hide these rows?
So for example, these rows in yellow should not be shown as they have no values:

Helpforum empty rows
Helpforum empty rows1122×508 112 KB

This is the DAX formula I use to get the values in:

Annual Totals =

SWITCH( TRUE(),

‘Metric Selection’[Metric Selected] = “Bedragen”, FORMAT([Selected Year Actuals],“€#,0”),

‘Metric Selection’[Metric Selected] = “Percentage tov omzet”, [Selected Year Actuals %],

‘Metric Selection’[Metric Selected] = “Index tov vorig jaar”, FORMAT([Actuals Index],“0”) ,

BLANK())

I have tried to add a filter to this table with “Annual Totals <> blank”, but this is not working.

It would be great if somebody can help me!

Best regards, Marieke

2

Hi @marieke

Can you check if “Show items with no data” is enabled for any of the labels in the Rows section of the matirx?

image

1 Like
3

Hi Melissa,

Yes, that is enabled for any of the labels.
If I disable that, the table still looks the same…

Thanks,

4

Hey @marieke,

The row headers are coming from a disconnected table if i am not wrong. Since this table is not being filtered directly i doubt you can hide them but you can replace blanks as 0 .

Regards,
Hemant

5

Hi @marieke,

Can you supply a small sample file because I checked your other PBIX file (here) and when “Show items with no data” is disabled, all blank rows are hidden.

6

Hi @Melissa and @Hemantsingh , i have attached a copy of the file.
I tried to replace BLANK () bij 0 but that is also not working as I want…

Versie Helpforum blank rows.pbix (976.9 KB)

7

In my previous file the calculation for metric selected was not working at that moment. Perhaps that has caused the problem now?

8

Hi @marieke,

Thanks for providing a sample PBIX :+1:

Annual Totals = 
SWITCH( TRUE(),
    'Metric Selection'[Metric Selected] = "Bedragen", IF( FORMAT([Selected Year Actuals],"€#,0") ="", BLANK(), FORMAT([Selected Year Actuals],"€#,0")),
    'Metric Selection'[Metric Selected] = "Percentage tov omzet", [Selected Year Actuals %],
    'Metric Selection'[Metric Selected] = "Index tov vorig jaar", FORMAT([Actuals Index],"0"),
    0
)

The issue is caused by the FORMAT() function. When that returns a ‘blank’ it is actually returning an empty text string. So I added an IF statement checking for that “” and then returning a BLANK instead that resolves the issue.

I hope this is helpful.

2 Likes
9

Hi @Melissa ,

that is great, many thanks for your help again :slight_smile:

Much appreciated!!

1 Like