Hi Power BI experts,
Need help on creating calculated column using use relationship (Preferably DAX).
I have table like below
Customer | Joining Date | Start date | End Date | Time Stamp | Status | Code |
C1 | 2019-01-18 | 2019-01-18 | 9999-12-31 | 2019-08-16 15:43:06.496877 | 2 | A |
C1 | 2019-01-18 | 2019-01-18 | 9999-12-31 | 2019-08-16 15:43:06.496877 | 2 | A |
C2 | 2018-09-01 | 2018-09-01 | 9999-12-31 | 2019-06-21 15:43:06.496877 | 2 | A |
C3 | 2018-08-27 | 2018-08-27 | 9999-12-31 | 2018-08-27 09:55:56.456789 | 2 | A |
C3 | 2018-08-27 | 2018-10-01 | 2018-10-01 | 2018-09-30 11:15:06.67678 | 12 | D |
C4 | 2019-02-01 | 2019-02-01 | 2019-02-06 | 2019-08-19 15:43:06.496877 | 1 | W |
C4 | 2019-02-01 | 2019-02-01 | 9999-12-31 | 2019-08-19 15:43:06.496877 | 2 | A |
C5 | 2019-02-01 | 2019-02-01 | 9999-12-31 | 2019-08-19 15:43:06.496877 | 1 | A |
C5 | 2019-02-01 | 2019-02-01 | 2019-02-06 | 2019-08-19 15:43:06.496877 | 1 | D |
I need a calculated column based on below conditions.
| Conditions | | | |
Calculated Column Values | Status | Code | Start Date | End Date |
Active | 2 | A | Start Date <= Today() | End Date > Today() |
Pre-Active | 1 | A | Start Date <= Today() | End Date > Today() |
Closed | 12 | D | Start Date <= Today() | End Date < Today() |
Going on | 1 | W | | |
(I am thinking to use if function here but below scenario needs to use “USERELATIONSHIP” function I think so, because the x axis should change dynamically). Please read full scenario before your suggestion.
Resultant table:
Customer | Joining Date | Start date | End Date | Time Stamp | Status | Code | Result |
C1 | 2019-01-18 | 2019-01-18 | 9999-12-31 | 2019-08-16 15:43:06.496877 | 2 | A | Active |
C1 | 2019-01-18 | 2019-01-18 | 9999-12-31 | 2019-08-16 15:43:06.496877 | 2 | A | Active |
C2 | 2018-09-01 | 2018-09-01 | 9999-12-31 | 2019-06-21 15:43:06.496877 | 2 | A | Active |
C3 | 2018-08-27 | 2018-08-27 | 9999-12-31 | 2018-08-27 09:55:56.456789 | 2 | A | Active |
C3 | 2018-08-27 | 2018-10-01 | 2018-10-01 | 2018-09-30 11:15:06.67678 | 12 | D | Closed |
C4 | 2019-02-01 | 2019-02-01 | 2019-02-06 | 2019-08-19 15:43:06.496877 | 1 | W | Going on |
C4 | 2019-02-01 | 2019-02-01 | 9999-12-31 | 2019-08-19 15:43:06.496877 | 2 | A | Active |
C5 | 2019-02-01 | 2019-02-01 | 9999-12-31 | 2019-08-19 15:43:06.496877 | 1 | A | Pre-Active |
C5 | 2019-02-01 | 2019-02-01 | 2019-02-06 | 2019-08-19 15:43:06.496877 | 1 | D | Closed |
Visual Scenario:
Based on the created column I will use slicer in visual. I will also use year slicer from calendar dim table.
Visual: I need a column chart with date (I will use calendar dim table here) as x-axis and count of customers as Y axis.
If I select Active and Pre-active, it should have x-axis date based on Joining date.
If I select Going on, it should have x-axis date based on Time stamp.
If I select closed, it should have x-axis date based on Start date.
Please help me with your suggestions.
Thanks in advance.
