Get value in function of a metric (filtered on an interval)

[JCG99]

I have category of values that I can display on a table. Those values are listed on function of a distance, and often, there is blank value at different interval. I want to list all different values ordered by the interval.

Currently, by using a min/max measure, I get the following result:

from | to | value 

0        100     NULL

10        20     1

30        50     2

70        80     3

What I want to get in the table is the following:

from | to | value 

0        10     NULL

10        20     1

20        30   NULL

30        50     2

50        70   NULL

70        80     3

70       100  NULL 

Any idea of how could I achieve this ? 

[Anonymous]

Hi  @JCG99 ,

In your scenario, you could check that [value] is a text column and that the only occurrence of Null is a string, while the other Nulls that don't occur are blank().

You might also consider using 0 instead of Null:

Test =
IF(
    [value]=BLANK(),0,[value])

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[JCG99]

Sure, here is what I have initially:

0	1
1	2	V
2	3	V
3	4

But what I would like to get is the following:

0	4
1	3	V

By usung min/max measure I get this result:

0	1
1	3	V
3	4

[AlexisOlson]

Does showing items with no data help?

https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data