cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
luiscb Regular Visitor
Regular Visitor

Minimum value problem with null value on high level aggregation

 

Hello everyone,

I have the following code

Conditions_Legend = 
var X = SUMMARIZE(fact,fact[country],fact[WeekText],"MIN", RANDBETWEEN(1,40)+0)
var Min_Country = MINX(X,[MIN])

return
Min_Country


Which applied to a matrix with WeekText as row headers and Country as Column Header produces the following:

 

table_countries.PNG

 

For Japan is NULL because for that specific week it does not hold data and my objective is when I take the country dimension from the matrix header, that the minimum retrieved to be 0 due to Japan.

Unfortunately, when I take the country dimension of the Matrix, it does not recognize the null value as 0 and so puts the minimum number value, which is 5

countries_bad.PNG

I have played around with if statement to convert null to zero, but I end up having the same result.
Any ideas on how not to ignore the null value in the condition above?

Thanks!
Ps: I used random values and added a 0 to try and force a 0 to appear instead of null

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
luiscb Regular Visitor
Regular Visitor

Re: Minimum value problem with null value on high level aggregation

I have actually used the following approach and did what the trick:

var = SUMX(fact,SWITCH(TRUE(),ISBLANK([KPI Measure]),1,0))

So this problem is closed
2 REPLIES 2
Super User
Super User

Re: Minimum value problem with null value on high level aggregation

I would use IF(ISBLANK()) here.

 

Something along the lines of 

var Min_Country = IF(ISBLANK(MINX(X,[MIN])),0,MINX(X,[MIN]))

If it's blank, you give it a default value of zero, otherwise return the normal value.

Highlighted
luiscb Regular Visitor
Regular Visitor

Re: Minimum value problem with null value on high level aggregation

I have actually used the following approach and did what the trick:

var = SUMX(fact,SWITCH(TRUE(),ISBLANK([KPI Measure]),1,0))

So this problem is closed

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 277 members 2,750 guests
Please welcome our newest community members: