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
)
BIlix's avatar
BIlix
Icon for Helper II rankHelper II
3 years ago

Unfortunately not. Could I provide you with any other information which could help?

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

A picture of the table relationships might help.

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

     

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

    Do you have any filters or slicers on the other tables which could be affecting the results? Either connected to the fact table or connected to the project table ?

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

    No Filters are activated on the page-

    But the measure for calculating the StoryPoints is Calculated by Sum the StoryPoints from the FactTable, filtered by two filters from Dimension Tables which are connected one to many to FactTable.

    Calculate(Sum(Fact[Storypoints]), DimStatus[Status] IN {"..."}, DimType[Type] IN {"..."})

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

    I can't see where any problems might be coming from. All I can offer is some general tips on how I would proceed with debugging.

    Run the SUMMARIZE from the code in DAX Studio and see if the resulting table has the values you expect.

    Use Performance Analyzer to grab the code generated for your visual and run it in Tabular Editor 3 so that you can use the Debug Cell functionality to step into the code and see what the filter context is at each stage, and what the intermediate results are.

    Use EVALULATEANDLOG with DAX Debug Output to show the values and filters at different stages.

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

    Hello,

    unfortunately i did not manage to get any solution.



    DimDeskDistribution has a many to One Relation to DimProject
    DimProject has a One to Many Relationship to The FactTable
    the FactTable has a MAny to OnE Relationship to DimStatus
    The Storypoints are In The FactTable

    The Measure for StorypointsCompleted is: Storypoints Completed = CALCULATE(SUM(Facttable[StoryPoints]), DimStatus[Status] IN {"Closed", "Done/In Production"}) 
    Could it be because of the Filter Direction? there is only single filtering in the data model
    I tried to apply a Crossfilterfunction from DimDeskdistribution to DimProject but did not manage to get any results. 

    When i use the The measure and only Desk in Rows, it returns a single desk with the total amoutn of storypoints for all projects. ITs the desk with the highest ProjectID assignment

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

    Hello again,

    i managed to find a solution for the problem:

    Storypoint distributed to Desk = SUMX (
        CALCULATETABLE (
            SUMMARIZE (
                FILTER (
                    DimDeskDistribution,
                    NOT ( ISBLANK ( [StorypointMeasure] ) )
                ),
                DimDeskDistribution[Desk],
                DimDeskDistribution[DeskDistribution],
                DimProject[ProjectID],
                DimProject[Project]
            ),
            CROSSFILTER ( DimDeskDistribution[ProjectID], DimProject[ProjectID], Both )
        ),
        [StorypointMEasre] * DimDeskDistribution[DeskDistribution]
    )


    I now want to distirbute another measure to Desks in a similar way.
    The measure itself are summed up costs from a fact table.
    The Facttable is related many to one to a project2 Dimension.
    The project2dimension is related one to many to the project DImension.
    The Project Dimension is related one to many to The Deskdistribution DImension.

    No crossfiltering is enabled. I can not change the Relationships to crossfiltering and should not add more active relationships. Any Ideas how i could get a workaround for my Dax Function? Thanks in advance