Hi All,
I'm in need of some help.
First let me outline the data.
I have 2 columns Product & Days until Expiry date
I also have a calculated column which uses a switch statement that looks at the Days until Expiry date column and and assigns labels e.g. if the Product has more than 100 days until expiry then it will be assigned the label "No Orders required"
Current Code
ProductLabels =
SWITCH (
TRUE(),
isblank('DF'[Days until Expiry date]),"No Date available",
'DF'[Days until Expiry date]>=100, "No Orders required",
'DF'[Days until Expiry date]<100 "Orders Required"
)
The issue is that this month we might label products with < 100 days until expiry as "Orders Required" and next month this might be pushed to < 150 days
I want to create a slicer which will have 100,150,200,250 days. when the user selects one I want the calculated column above to be dynamically adjusted.
e.g. it will look like this
ProductLabels =
VAR SV = SELECTEDVALUE(SlicerTable[Values])
SWITCH (
TRUE(),
isblank('DF'[Days until Expiry date]),"No Date available",
'DF'[Days until Expiry date]>=SV , "No Orders required",
'DF'[Days until Expiry date]<SV ,"Orders Required"
)
And the final result will be a distinct count of the products by label
e.g
Label Distinct Count of Products
No Change 10
Orders Required 20
No Orders Required 30
I'm aware that selectedvalue cannot be used within calculated columns so I'm looking to create a measure that contains a distinct count.
Thanks in advance
