Forum Discussion

reddy421_hc's avatar
reddy421_hc
Frequent Visitor
6 years ago
Solved

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). ...
Need Help
  • reddy421_hc's avatar
    reddy421_hc
    6 years ago

    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]
    )
    )
v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
reddy421_hc
Frequent Visitor
6 years ago

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