topic Re: Calculate Values based on a Distribution Key in a linked Table in DAX Commands and Tips

Calculate Values based on a Distribution Key in a linked Table

BIlix — Wed, 12 Jul 2023 13:40:58 GMT

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.

THe Fact Table is linked many to one to A Project Dimension. The Project Dimension is linked one to many to a Distribution Dimension via ProjectID
Every ProjectID has one or multiple Desks assigned in the DIstribution Dimension and also a Ditribution/allocatiion KEy.
The final calculation should exemplary be like this:
PRoject 1 is linked to Desk 1, Desk 2, Desk 3. Desk 1 has a 0.2, Desk 2 has 0.2, Desk 3 has 0.6 Allocation

Exemplarily Project 1 has 10 assigned Story Points. The Storypoints should be multiplied by the Desk Distribution Key. Desk 1 has 2 SP, Desk 2 has 2 SP, Desk 3 has 6 SP.

My Current Measure
SUMX(Values(DimDeskDistribution[Desk]), [Storypoint Measure] * Calculate(max(DimDeskdistribution[DeskDistribution])))

When i just put Projects in a MAtrix visual, the Storypoints are distributed correctly and the Total value is correct.
But when i just put Desks in the visual, the results are wrong. For example there are 2 Desks where the linked Project has a distribution of 1. These desks have a value of 574 (total amount of story points for my data). But they should only have the values of the storypoints that were assigned to the project. Also in both Variants it shows the a total amont of 2870 SP, which is wrong(

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Wed, 12 Jul 2023 14:14:05 GMT

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 )

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Wed, 12 Jul 2023 18:32:07 GMT

Thanks for your reply!

Its working great, in a matrix Visual I can breakdown the Projects to the Desks. Also the allocation of the Storypoints is correct.
Could you explain how the measure can be adjusted to also show proper results in a Visual where only the Desk Column is included? I also need a visual where The desks and their allocated storypoints are included.

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 08:28:28 GMT

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.

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 13 Jul 2023 09:26:06 GMT

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

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 09:54:09 GMT

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 )

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 13 Jul 2023 10:28:40 GMT

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

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 10:53:52 GMT

How about

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

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 13 Jul 2023 12:30:19 GMT

In the PRoject View the results are correct.

when selecting desk, there are also results showing. But they are way too high

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 13:28:12 GMT

Can you share a PBIX with any confidetial information removed ?

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 13 Jul 2023 14:33:49 GMT

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

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 14:44:38 GMT

A picture of the table relationships might help.

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 13 Jul 2023 14:49:17 GMT

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Thu, 13 Jul 2023 15:09:38 GMT

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 ?

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Fri, 14 Jul 2023 06:55:27 GMT

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 {"..."})

Re: Calculate Values based on a Distribution Key in a linked Table

johnt75 — Fri, 14 Jul 2023 09:16:38 GMT

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.

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Wed, 19 Jul 2023 15:00:30 GMT

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

Re: Calculate Values based on a Distribution Key in a linked Table

BIlix — Thu, 24 Aug 2023 07:27:38 GMT

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