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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
reddy421_hc
Frequent Visitor

power bi create a calculated column from different relationship

Hello, I have a few date fields in my fact table. I created a date dimension table and assigned all the relationships to them.

I Know only one will be active out of all. ( pic is shown below).

1.png

Can someone help me in calculating a running total column using a different relationship?

Below is one Calculated column I created ( which is working since I used this with an active relationship.

RunningTotalCases =
CALCULATE(
SUM(CaseDataAnalysisReport[IsCase]),
FILTER(
ALLSELECTED(CaseDataAnalysisReport),
CaseDataAnalysisReport[DateConvertedToCase]<=MAX(CaseDataAnalysisReport[DateConvertedToCase])
)
)
 

Now I want to create one more running total.(Using the date Hos

Below is what I have ( this is now working).

 

RunningTotalHospitalized =
CALCULATE(
SUM(CaseDataAnalysisReport[IsHospitalized]),
FILTER(
ALLSELECTED(CaseDataAnalysisReport),
CaseDataAnalysisReport[DateHospitalized]<=MAX(CaseDataAnalysisReport[DateHospitalized])
)
)

Thank you.

1 ACCEPTED SOLUTION

Below is how I got what I needed.

Thank you all for your help.

I really Love this community.

Cumulative Total Hospitalized =
CALCULATE(
SUM ( CaseDataAnalysisReport[IsHospitalized]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    ),
USERELATIONSHIP (CaseDataAnalysisReport[DateHospitalized],'Date'[Date]
)
)

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

is second either use all(Date) or use crossfilter (option none) to remove join with the other date

 

Greg_Deckler
Super User
Super User

Tough to be specific, but in general you need to use USERELATIONSHIP.

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thank you for trying to help me.

 

I used this below expression to calculate and still I am not getting what I needed.

Any help is really appreciated.

 

Measure 2 =
CALCULATE (
SUM ( CaseDataAnalysisReport[IsHospitalized]),
USERELATIONSHIP (CaseDataAnalysisReport[DateHospitalized],'Date'[Date]
)
)
 
Result:
1.png

Hi, @reddy421_hc 

 

Based on your description, I created data to reproduce your scenario.

TableA:

d1.png

TableB:

d2.png

 

The relationship between two tables is inactive.

d3.png

 

You may create a measure as below. It is important that you should write 'USERELATIONSHIP(TableB[UserId],TableA[UserId])' instead of 'USERELATIONSHIP(TableA[UserId],TableB[UserId])'

 

Measure = 
CALCULATE(
    SUM(TableB[Amount]),
    USERELATIONSHIP(TableB[UserId],TableA[UserId])
)

 

 

Result:

d4.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Below is how I got what I needed.

Thank you all for your help.

I really Love this community.

Cumulative Total Hospitalized =
CALCULATE(
SUM ( CaseDataAnalysisReport[IsHospitalized]),
    FILTER(
        ALLSELECTED('Date'[Date]),
        ISONORAFTER('Date'[Date], MAX('Date'[Date]), DESC)
    ),
USERELATIONSHIP (CaseDataAnalysisReport[DateHospitalized],'Date'[Date]
)
)

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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

Top Kudoed Authors