Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Fabric Community site will be in read-only mode on Monday, Feb 24 from 12:01 AM to 8 AM PST for scheduled upgrades.

Reply
bijaymaharjan
New Member

Toggling between Active and Inactive Relationship in Power BI

Thursday

Hi all,

Due to the proprietory reason I can't disclose the full dataset but I am creating some mock tables with data to let you know my issue. I really need help, I have exhausted all the options that I can think of. Here is a sample data set, it includes the PolicyNumber, Effective Date of the Policy, Transaction Date and the Premium.

PolicyNumberEffective DateTransactionDatePremium
xyz2/7/20242/29/2024200
xyz2/7/20243/2/2024100
xyz2/7/20243/5/2024100

 

In the power bi, I have this fact data and a Calendar table. The relationship between Calendar[Date] and TransactionDate is an Active one and Calendar[Date] and Effective Date is an inactive one. 

bijaymaharjan_0-1740081245011.png

I have following DAX set up to calculate the Premium with respect to Transaction and Effective Date. 
EP = SUM(premium_data[Premium]) 
EP PolicyYear = CALCULATE(EP, USERELATIONSHIP(Calendar[Date], premium_data[EffectiveDate])).
Here is the sample result that this will produce:

MonthEPEP PolicyYear
Feb200400
March2000

This is all fine but when I add a Date slicer in the mix, and slice the date to show data from 2/1/2024-2/29/2024, the EP PolicyYear still shows 400. I need to show 200.
Please help!

 

Message 1 of 6
165 Views
0
1 ACCEPTED SOLUTION
MarkLaf
Solution Sage
Solution Sage

Thursday

Can you add a 1-2 more instances into your dummy data to show how the measure should behave? Maybe provide a little more context? I wrote a measure that provides the desired output, but I'm not sure this actually is what you want.

 

 

EP PolicyYear = 
SUMX(
	CALCULATETABLE(
		VALUES(premium_data[EffectiveDate]),
		USERELATIONSHIP(
			'calendar'[Date],
			premium_data[EffectiveDate]
		)
	),
	CALCULATE(
		SUM(premium_data[Premium]),
		CALCULATETABLE(
			ALLSELECTED('calendar'[Date])
		)
	)
)

 

 

Do I understand correctly that you want: sum of premiums within visual's month per their effective date, but then the outside date slicer should filter policies by their transaction date? Does the date slicer still filter the months in the visual? Example:

 

PolicyNumberEffective DateTransactionDatePremium
xyz7/1/20247/1/202450
xyz8/1/20247/1/202475

 

If the date slicer is set to 7/1/2024..7/31/2024, should the visual show:

 

MonthEPEP PolicyYear
Jul12550
Aug 75

 

OR

 

MonthEPEP PolicyYear
Jul12550

(this is what the measure above is doing - ie the date slicer is still filtering the date month in the visual)

View solution in original post

Message 4 of 6
112 Views
5 REPLIES 5
MarkLaf
Solution Sage
Solution Sage

Thursday

Can you add a 1-2 more instances into your dummy data to show how the measure should behave? Maybe provide a little more context? I wrote a measure that provides the desired output, but I'm not sure this actually is what you want.

 

 

EP PolicyYear = 
SUMX(
	CALCULATETABLE(
		VALUES(premium_data[EffectiveDate]),
		USERELATIONSHIP(
			'calendar'[Date],
			premium_data[EffectiveDate]
		)
	),
	CALCULATE(
		SUM(premium_data[Premium]),
		CALCULATETABLE(
			ALLSELECTED('calendar'[Date])
		)
	)
)

 

 

Do I understand correctly that you want: sum of premiums within visual's month per their effective date, but then the outside date slicer should filter policies by their transaction date? Does the date slicer still filter the months in the visual? Example:

 

PolicyNumberEffective DateTransactionDatePremium
xyz7/1/20247/1/202450
xyz8/1/20247/1/202475

 

If the date slicer is set to 7/1/2024..7/31/2024, should the visual show:

 

MonthEPEP PolicyYear
Jul12550
Aug 75

 

OR

 

MonthEPEP PolicyYear
Jul12550

(this is what the measure above is doing - ie the date slicer is still filtering the date month in the visual)

Message 4 of 6
113 Views
bijaymaharjan
New Member
In response to MarkLaf

Thursday

I just tried your code and this is actually working. Thank you

Message 6 of 6
89 Views
0
bijaymaharjan
New Member
In response to MarkLaf

Thursday

Hi, I added more data to help you visualize the output I am looking for. 

PolicyNumberEffectiveDateTransactionDatePremium
ABC2751/3/20241/31/202431.5956
ABC2751/3/20241/31/20240
ABC2751/3/20241/31/20245.04918
ABC2751/3/20241/31/20240
ABC2751/3/20242/29/20242.06011
ABC2751/3/20242/29/202413.6284
ABC2751/3/20242/29/20240
ABC2751/3/20242/29/20240
ABC2751/3/20242/29/20240
ABC2751/3/20242/29/2024247.53
ABC2751/3/20242/29/20240
ABC2751/3/20243/31/20240
ABC2751/3/20243/31/20240
ABC2751/3/20243/31/2024264.601
ABC2751/3/20243/31/20240
ABC2751/3/20243/31/202428.0355
ABC2751/3/20243/31/202444.3825
ABC2751/3/20244/30/202433.8525
ABC2751/3/20244/30/20240
ABC2751/3/20244/30/20245.40984
ABC2751/3/20244/30/202442.9508
ABC2882/5/20242/29/20246.81421
ABC2882/5/20242/29/20240
ABC2882/5/20242/29/202462.6749
ABC2882/5/20243/31/202415.2459
ABC2882/5/20243/31/202443.112
ABC2882/5/20243/31/20246.35246
ABC2882/5/20244/30/20242.45902
ABC2882/5/20244/30/20247.04918
ABC2882/5/20244/30/20240
ABC2882/5/20244/30/202464.8361

 

Now, based on the measure I have mentioned above. If i create a matrix this is the result I get. 

bijaymaharjan_0-1740104144115.png

But when I move the slicer, to 1/1/2024 - 2/29/2024, the EP PolicyYear does not filter out. What I need is whenever a slicer is moved, the table should filter the Transaction Date to show data from 1/1/2024-2/29/2024. Once the filter is applied, then I need the EP PolicyYear to calculate. 
This is the output I get:

bijaymaharjan_1-1740104341982.png

 

This is the output I need:

YearEPEPPolicyYear
January

36.64

299.86
February332.7169.49



Message 5 of 6
96 Views
0
Alex_Sawdo
Resolver I
Resolver I

Thursday

Try wrapping the CALCULATE into another CALCULATE and adding CROSSFILTER: 

CALCULATE(
    CALCULATE(
        EP, 
        USERELATIONSHIP(
            Calendar[Date], 
            premium_data[EffectiveDate]
        )
    ),
    CROSSFILTER(
        Calendar[Date],
        prem[TransactionDate],
        None
    )
)

The logic of this is enabling the inactive relationship, then disabling the active relationship with the second calculate. 

Message 2 of 6
142 Views
0
bijaymaharjan
New Member
In response to Alex_Sawdo

Thursday

I tried this, the EP PolicyYear does not update when the slicer is moved. I added some more data in the comment above. 

Message 3 of 6
96 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

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

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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

View All