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

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 Member
Member

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

View solution in original post

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 Member
Member

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 203 members 2,547 guests
Please welcome our newest community members: