Forum Discussion

BIlix's avatar
BIlix
Icon for Helper II rankHelper II
3 years ago
Solved

Calculate Values based on a Distribution Key in a linked Table

Hello Community, I want to Calculate Values from my Fact Table based on another Table. I have Story Points in my Fact Table. I already have a Measure to Calculate the StoryPoints i need.  TH...
  • johnt75's avatar
    johnt75
    3 years ago

    Try

    Current Measure =
SUMX (
    VALUES ( DimDeskDistribution[Desk] ),
    VAR CurrentProject =
        SELECTEDVALUE ( DimDeskDistribution[Project ID] )
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)
johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

I think you could amend it to

Current Measure =
SUMX (
    VALUES ( DimDeskDistribution[Desk] ),
    VAR CurrentProject =
        RELATED ( DimDeskDistribution[Project ID] )
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)

That should work in all scenarios.

BIlix's avatar
BIlix
Icon for Helper II rankHelper II
3 years ago

Unfortunately it does not work. 

At 'RELATED' I get an error that the column does not exist or does not have any relationship to any table in the context

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

    Try

    Current Measure =
SUMX (
    SUMMARIZE (
        DimDeskDistribution,
        DimDeskDistribution[Desk],
        DimDeskDistribution[Project ID]
    ),
    VAR CurrentProject = DimDeskDistribution[Project ID]
    VAR Result =
        CALCULATE (
            [Storypoint Measure],
            TREATAS ( { CurrentProject }, DimProject[Project ID] )
        )
            * CALCULATE ( MAX ( DimDeskdistribution[DeskDistribution] ) )
    RETURN
        Result
)
    • BIlix's avatar
      BIlix
      Icon for Helper II rankHelper II
      3 years ago

      Thanks again for your help. Unfortunately the error persists.

      When the measure is used in a visual it only shows values for Project. All values for desk are blank or not recognized

      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        How about

        Current Measure =
SUMX (
    SUMMARIZE (
        DimDeskDistribution,
        DimDeskDistribution[Desk],
        DimProject[Project ID],
        DimDeskdistribution[DeskDistribution]
    ),
    [Storypoint Measure] * DimDeskdistribution[DeskDistribution]
)