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

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