power bi create a calculated column from different relationship

[reddy421_hc]

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).

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.

[amitchandak]

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

[Greg_Deckler]

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

[reddy421_hc]

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:

[v-alq-msft]

Hi, @reddy421_hc 

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

TableA:

TableB:

The relationship between two tables is inactive.

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:

Best Regards

Allan

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

[reddy421_hc · 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]

)

)