Measure - SUM (with multiple filters) exclude blanks

[diederd]

Hello Power BI Community

I'm trying to perform a SUM calculation which has multiple filters applied; however when needing to display the results in a table visual - it appears to be including all values including any that are blank? DAX measure is as follows:

feesPaidBuy =

IF(ISFILTERED(CalendarDate[Date]),

SWITCH(SELECTEDVALUE('Currency'[baseCurrency]),

"EUR", CALCULATE(SUM(tblTransaction[EUR Amount (Fees Paid)]),

tblTransaction[tblKey statistics.Event]="M&A",

tblTransaction[Role]="M&A - Buyside",

tblTransaction[Audit Complete] = "YES"),

"USD", CALCULATE(SUM(tblTransaction[USD Amount (Fees Paid)]),

tblTransaction[tblKey statistics.Event]="M&A",

tblTransaction[Role]="M&A - Buyside",

tblTransaction[Audit Complete] = "YES")),

SWITCH(SELECTEDVALUE('Currency'[baseCurrency]),

"EUR", CALCULATE(SUM('tblTransaction'[EUR Amount (Fees Paid)]),

tblTransaction[tblKey statistics.Event]="M&A",

tblTransaction[Role]="M&A - Buyside",

'tblKey statistics'[Date] <= TODAY() && 'tblKey statistics'[Date] >= TODAY()-365,

tblTransaction[Audit Complete] = "YES"),

"USD", CALCULATE(SUM('tblTransaction'[USD Amount (Fees Paid)]),

tblTransaction[tblKey statistics.Event]="M&A",

tblTransaction[Role]="M&A - Buyside",

'tblKey statistics'[Date] <= TODAY() && 'tblKey statistics'[Date] >= TODAY()-365,

tblTransaction[Audit Complete] = "YES")))

The table should result in data where feesPaid is not blank; yet it appears to be displaying all values. Any help/guidance would be much appreciated.

Figure 1 is how it's currently being displayed.

Figure 2 is how I would like for it to be displayed.

Thanking you in advance.

[v-lid-msft]

Hi @diederd ,

We can try to put the measure into Visual Filter and set it greater than 0 if you do not want to show the row that feesPaidBuy is blank

Also we can try to disable "Show items with no data" for  this measure.

Best regards,

[diederd]

Hello v-lid-msft

Thank you for your response; whilst I have tried the visual filter route (setting Fees Paid to is not blank) - this works within the PBI Desktop application, it's when this is published to a SharePoint webpart that the filter seems to disappear.

I'm somehow thinking I need to include this in the DAX query as to eliminate the blank values from displaying - just don't know how?

Thanks again for your help.

[v-lid-msft]

Hi @diederd ,

Sorry for late reply, Have you issue be resolved? Can you see the result of visual filter in Share point? Does the visual filter applied in Power BI Service?

Best regards,