Hi,
I have a dataset with the year of birth from customers and the transaction date.
The year of birth table is polluted since I got next to normal year of birth also 2, 560 and 2030 but also textual values.
Since we receive this data from other organizations and it is provided to me through live connection with a tabular model I can't correct or clean this data.
What I want to do is writing a measure that calculates the age of a customer on the moment the person did a transaction (to find out how many transaction (and average) are made by each age).
So I wrote the DAX query below. Only I have a few issues and some questions with that right now. Hopefully somebody can help we this.
1. In the calculating of the age I escape the textual values. But I do want to give them the age value "Onbekend". How do I do this?
2. In the output I see that ages above 100 go to the category "Onbekend". How is this possible since my variable age_c isn't being called after the return?
3. I see some strange values in the output. 0000 converts to the age 0, 1907 to -1907, 1909 to -1909 (while 1908 gives me a correct value), 4 to -4, 5 tot -5 while 6, 7 and 8 give me the right age value again. How is this possible?
4. In my age categories everything goes like it should be, except for the age value 0 which is mapped to "Onbekend" instead of "0-4".
5. Can this DAX query be written better/shorter e.g. by swapping if for switch?
Many thank!
Kind regards,
Hans
Age category =
VAR
Age = CALCULATE (
(YEAR(MAX(Fact_Transactie[TransactieDatum] ))) - MAX(DimLidHis[Geboortejaar]);
FILTER ( VALUES ( DimLidHis[Geboortejaar] ); NOT ( ISERROR ( DimLidHis[Geboortejaar] + 0 ) ) ) )
Var
Age_c = IF(
Age <= 100 && Age >= 0;
Age;
"Onbekend"
)
Return
IF(
Age >= 0 && Age < 4 && Age <> BLANK();
"0-4";
IF(
Age >= 4 && Age =< 10 && Age <> BLANK();
"4-10";
IF(
Age >= 11 && Age < 18 && Age <> BLANK();
"11-18";
IF(
Age >= 18 && Age <> BLANK() && ISNUMBER(Age);
"Volwassen";
"Onbekend"
)
)
)
)
