Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need help with Dax.
There are 4 column in my test table as follows:
-ID: every customer have unique ID, every row means one subscription
-start of service: The start of the subscription
-end of service: the end of the subscription
-number of service: it has 3 possible value: 1 if the customer have 1 channel, 2 if 2 channel and 3 if 3 channel
I would like to create a calculate column, which indicate that the customer changed to a smaller subsciption or not (it means change to a fewer channel). I would like to see 0 if not and 1 if yes, so I would like to get the following table with Changing column:
So the customer 1 (in the 4th row) get a 1 value, because he changed his previous subscription from 1 to 2.
Any help would be greatly appreciated.
Solved! Go to Solution.
Hi @bog12345,
You could create the calculated column with the formula below.
Chaging =
VAR previous_date =
CALCULATE (
MAX ( 'Table'[Start of service] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Start of service] < EARLIER ( 'Table'[Start of service] )
)
)
VAR previous_Number =
LOOKUPVALUE (
'Table'[Number of Service],
'Table'[ID], 'Table'[ID],
'Table'[Start of service], previous_date
)
RETURN
IF (
previous_Number = BLANK ()&&'Table'[ID]='Table'[ID],
0,
IF ( previous_Number = 'Table'[Number of Service]&&'Table'[ID]='Table'[ID], 0, 1 )
)
Then you will get the output you expected.
Hope it can help you!
Best regards,
Cherry
Hi @bog12345,
You could create the calculated column with the formula below.
Chaging =
VAR previous_date =
CALCULATE (
MAX ( 'Table'[Start of service] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[ID] ),
'Table'[Start of service] < EARLIER ( 'Table'[Start of service] )
)
)
VAR previous_Number =
LOOKUPVALUE (
'Table'[Number of Service],
'Table'[ID], 'Table'[ID],
'Table'[Start of service], previous_date
)
RETURN
IF (
previous_Number = BLANK ()&&'Table'[ID]='Table'[ID],
0,
IF ( previous_Number = 'Table'[Number of Service]&&'Table'[ID]='Table'[ID], 0, 1 )
)
Then you will get the output you expected.
Hope it can help you!
Best regards,
Cherry
Thanks, it works 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
97 | |
80 | |
67 | |
60 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |