Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have one column which contains date in 'DD/MM/YYYY' format. That column data type is 'String'.
I want to take count of records where year of date >= 2017.
How to write expression in measure?
Can someone please help to resolve it
Thanks,
Dhanashree
That's tricky with string dates. If your data set is small, you can use Sum(If()):
Count(Distinct If(Right(DateS, 4) >= 2017, IDS))
But this may perform poorly in a large data model. Replace DateS and IDS with valid fieldnames.
The better approach is to convert the date string field to a proper date type (which is a numeric value formatted as a date) during load:
LOAD ...
Date(Date#(DateS, 'DD/MM/YYYY')) as Date,
Year(Date#(DateS, 'DD/MM/YYYY')) as Year,
...
Then your expression would be:
Count({<Year = {">=2017"}>} Distinct IDS)
Thanks Jonty 🙂 It's working