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

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.

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

5 Replies

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

         

         

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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