Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a table that contains a column with integer values and other empty ones (Blank), but I would like to be able to fill the empty values with -1 when there is a value greater than 0 in a the product A, and 0 in case it is 0, such as the second table shows:
Store | Product | Value |
1 | A | 1 |
1 | B | |
1 | C | |
2 | A | 2 |
2 | B | |
2 | C | |
3 | A | 0 |
3 | B | |
3 | C |
Store | Product | Value |
1 | A | 1 |
1 | B | -1 |
1 | C | -1 |
2 | A | 2 |
2 | B | -1 |
2 | C | -1 |
3 | A | 0 |
3 | B | 0 |
3 | C | 0 |
Is it possible to occupy a conditional within the EARLIER function, or is there another way to do it in DAX?
Solved! Go to Solution.
Hi, @nicolasvc
You can try creating new calculated columns and measures to fill in blank values.
New value =
IF (
[Value] <> BLANK (),
[Value],
IF (
CALCULATE (
MIN ( 'Table'[Value] ),
FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
) > 0,
-1,
IF (
CALCULATE (
MIN ( 'Table'[Value] ),
FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
) = 0,
0
)
)
)
2. Measure
Measure =
IF (
MAX ( 'Table'[Value] ) <> BLANK (),
MAX ( 'Table'[Value] ),
IF (
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
) > 0,
-1,
IF (
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
) = 0,
0
)
)
)
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @nicolasvc
You can try creating new calculated columns and measures to fill in blank values.
New value =
IF (
[Value] <> BLANK (),
[Value],
IF (
CALCULATE (
MIN ( 'Table'[Value] ),
FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
) > 0,
-1,
IF (
CALCULATE (
MIN ( 'Table'[Value] ),
FILTER ( 'Table', [Store] = EARLIER ( 'Table'[Store] ) && [Product] = "A" )
) = 0,
0
)
)
)
2. Measure
Measure =
IF (
MAX ( 'Table'[Value] ) <> BLANK (),
MAX ( 'Table'[Value] ),
IF (
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
) > 0,
-1,
IF (
CALCULATE (
MAX ( 'Table'[Value] ),
FILTER ( ALL ( 'Table' ), [Store] = MAX ( 'Table'[Store] ) && [Product] = "A" )
) = 0,
0
)
)
)
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is Value a regular column/calculated column in the model table or a measure in the visual?
If it's a regular column then it cannot be modified with DAX, but it should be possible with M. Alternatively an additional calculated column can be added that will have the blanks filled.
If it's a calculated column/measure then what's its syntax?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |