topic Sum measure with Null or blank value in DAX Commands and Tips

Sum measure with Null or blank value

Anonymous — Fri, 08 Apr 2022 09:33:28 GMT

Hi,

I have a measure that sets the profit target (in %) for each project. However some projects are not entitled to a target and therefore I'd like to set-up "N/A", or "blank", or "null", what ever works. Note that 0 is a target (no profit basically) so 0 cannot be used instead of blanks.

This is the measure

Profit target = SWITCH(LEFT(SELECTEDVALUE(dProjects[Project No.]),5),"T4035",0.25,"T4042",0.0,"T4045",0.0,"T4048",0.0,"T4051",0.25,"T4053",-0.25,"T4056",0.25,"null value or something").

Then I have another measure that will make a calulation based on "profit target".

Income target =

SUMX (

dProjects,

('dProjects'[Profit target] + 1 ) * 'fForecastUSD'[Spendings]

)

The problem is that when I enter anything else than a number at the end of the switch function in the first measure (here represented by "null value or something") "income target" returns an error.

How do i get the "income target" to ignore non numeric values?

thanks for your help.

Re: Sum measure with Null or blank value

johnt75 — Fri, 08 Apr 2022 11:31:58 GMT

You can either use BLANK() as the final argument to SWITCH or don't have any final argument, as the default behaviour of SWITCH is to return a blank value if none of the conditions are met.

Re: Sum measure with Null or blank value

Anonymous — Mon, 11 Apr 2022 01:11:49 GMT

Thanks johnt75. I made a table of the returned values of the swtich, and BLANK() is actually returned as "0". Any idea?

Re: Sum measure with Null or blank value

johnt75 — Mon, 11 Apr 2022 08:45:26 GMT

can you post the code?

Re: Sum measure with Null or blank value

Anonymous — Tue, 12 Apr 2022 11:57:40 GMT

my bad discard the previous messages, it actually return blank, and I was looking at a measure i made that calculates the target and sets o if blank. My problem is not fundamentally resolved because my totals do not get calculated, but I found a workaround which is to split between 2 tables, one containing projects with targets, and the other which is containing projects without targets. I 'll close the ticket and reopen one with the new issues i'll face.

Re: Sum measure with Null or blank value

v-yalanwu-msft — Tue, 12 Apr 2022 04:28:13 GMT

Hi, Anonymous ;

You could modify it.

Income target = IF([Profit target]<>"null value or something", SUMX ( dProjects, ('dProjects'[Profit target] + 1 ) * 'fForecastUSD'[Spendings] ))

The final output is shown below:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sum measure with Null or blank value

Anonymous — Tue, 12 Apr 2022 08:30:58 GMT

Thanks, i'll try that

Re: Sum measure with Null or blank value

Anonymous — Tue, 12 Apr 2022 11:44:40 GMT

I have tryed, but i actually have to update all the measures and formulas that use the profit target as an argument with an IF, which is pretty inconvinient, and my totals are not calculated anymore.

Re: Sum measure with Null or blank value

v-yalanwu-msft — Wed, 13 Apr 2022 01:46:15 GMT

Hi, Anonymous ;

You can fix total with the ISINSCOPE function. If not, can you share a simple file and the output you want?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.