Conditional logic is a core aspect of any report, most often we see something similar to formatting the output based on some condition or value. However we can use IF function in DAX for more than just changing the color of a value. In our previous post we explored how to use FIND. In this example we are going to use FIND in combination with IF to add some logic to the out of the measure.
In this example we are going to assume all other colors are out of stock and only the products which are blue in color are in Stock. The formula for this would look like below
ExFindBlueStock = IF(
find("Blue",'Dimension Stock Item'[Stock Item],1,-1)<0,
"Not in Stock",
"In Stock"
)
As you can see from the above example it is the functional equivalent of the CASE statement we use in SQL Server. The end result when viewed in the report looks as shown below
The SQL equivalent of the above function would be similar to
Select case when item like ‘%blue%’ then ‘in Stock’ else ‘not instock’ end
From orders
SELECT CASE WHEN item LIKE '%blue%'
THEN 'in Stock'
ELSE 'not instock'
END
FROM orders
Please Consider Subscribing
