Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
TARANEESH
Frequent Visitor

USERELATIONSHIP in calculated column and dynamically Changing axis in visual

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.

2 REPLIES 2
Anonymous
Not applicable

Hi buddy,

Looking at what you are trying to achieve, have you considered using a Matrix visual?

I think, I know I might be wrong, that using a matrix, slicer/s, date filter and a measure would probably resolve the issue for you…

Just a thought 😊

Kinds regards

Hicham

@Anonymous 

 

Thanks for your suggestion.

 

But I want to use barchart with dynamically changing x axis.

 


@Anonymous wrote:

Hi buddy,

Looking at what you are trying to achieve, have you considered using a Matrix visual?

I think, I know I might be wrong, that using a matrix, slicer/s, date filter and a measure would probably resolve the issue for you…

Just a thought 😊

Kinds regards

Hicham



@Anonymous wrote:

Hi buddy,

Looking at what you are trying to achieve, have you considered using a Matrix visual?

I think, I know I might be wrong, that using a matrix, slicer/s, date filter and a measure would probably resolve the issue for you…

Just a thought 😊

Kinds regards

Hicham


 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.