I am using this expression but I cannot have type= “Decimal”. How do I change this expression to be able to get a “decimal” type?
SELECT(Historical DSO Downloaded Table[DSO],
AND(
[Date] = TODAY(),
[Branch] = “Total”
)
)
select expression gives you a list of values.
Which type of column is DSO?
Decimal
So what’s the point? Select returns you a list of values and they’re not decimals? Print your result for better analyses plz
Sorry. So I have been using Type =“text” and yes the value is correct. However, I need to use this value to calculate another one. I cannot do that with type = “text”.
Just add a new column of type Decimal with the following formula, and use this new one in calculations.
1.0 * ANY(EXTRACTNUMBERS([DSO]))
Can it be Virtual?
Yes, but you don’t need any of them to be virtual, and actually, though you could, you shouldn’t make the first one with SELECT() virtual. Make both normal columns.
Keep the type of DSO column as text, as you had indicated. Otherwise, if you want to make the type as Number, then just multiply it by 1.0
Thank you for posting the table. This is not correct, but there’s no problem. Let’s forget about the expression for the moment and instead, please tell me what are you trying to achieve.
What exactly are you trying to do? Thanks.
Instead of a text I need a number or Decimal. Why? I want to compare this number/decimal to my lowest DSO (decimal) and Highest DSO (Decimal) so I can add color PER instance (Using a format rule).
I don’t understand the purpose of your expression and why you are generating a list using SELECT(). Based on my understanding of your posts, this does not make sense to me. Therefore, I’m asking you just to forget about the Types, expressions, etc. and just tell me in plain words what is that you are trying to do and to achieve what exactly. Thanks.
Sorry. So I have a table that shows:
Date, Branch, DSO.
I then have virtual columns to show:
Lowest DSO, Highest DSO, Average DSO.
I want to have a a format rule to compare the DSO (todays)to:
Lowest DSO, Highest DSO.
Not at all 
You don’t have to add virtual columns for this purpose. You can put the expressions directly in the relevant format rules. This is easy, but you will need to be aware of the performance impact of such practice. Therefore, first tell me please, you need to do this comparison only for the date of today or for all dates?
Yes! I ONLY want to compare TODAYS() DSO to the current lowest DSO and current highest DSO
In that case, I’d do the following:
-
Create a slice on the Historical DSO table that will contain only today’s records, name it for example todayDSO, using the following raw filter expression: [Date] = TODAY()
-
An example of format rules expressions would be:
AND(
IN([Key], todayDSO[Key]),
[DSO] = MAX(todayDSO[DSO])
// or MIN(todayDSO[DSO])
// or AVERAGE(todayDSO[Dso])
)
Here, you will only keep you existing Decimal DSO column and you won’t need additional ones.
Thank you so much for all your help!