Calculating the sum of a value on the DIM side of a star Schema

[Anonymous]

Hi all, 

Want some general thoughts on the below and optimsing a measure. 

Adopting a general star schema model with central FACT followed by DIMS attached. However, i'm interested in calculating the sum of the value in the DIM, specifically Lost time. For note the FACT is an audit table and the Lost time is an aggragation. Introducing the DIM value into the fact table would provide an incorrect result it would multiple the lost time but X number of Rows. 

I'm looking to get the aggregation of Lost time in One DIM table, and looking to group by this a variable in a seperate DIM. i.e. lost time in Table X wants to be filters by Group in table Y. 

Now to overcome this i've done the following: 

CALCULATE(SUM('DIMX'[Business Duration In Seconds]), CROSSFILTER('DIMX'[Sla Instance Id],factY[Sla Instance Id],both))

The idea being that you increase the expanded table by including the cross filter (note no applying cross filter in the model due to bad practice). 

Overall, is this the only solution? or are there better solutions to the issues? 

any thoughts appriciated. 

Thanks, 

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Sahir_Maharaj  offered and i want to offer some more information for user to refer to.

hello @Anonymous , based on your description, you want to see the aggregation of Lost time in One DIM table, if you don't want to change the filter direction of the dim table and the fact table to both, when you change the filter direction to both, it will affect the perfoamrnce of the data model, so the crossfilter() funciton is the best option,  the impact would be only when you use that particular measure in a report page, not always, you can refer to the following link.

DAX CrossFilter Function in Power BI: Write the Formula both-directional, but keep the relationship single-directional - RADACAD

Exploring the Power of CROSSFILTER in Power BI | by Andrew Hubbard | Microsoft Power BI | Medium

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Anonymous - Thanks for your input, I thought this may be the case but thought i'd check with the community. 

@Sahir_Maharaj - Thank you for your input as well, it was much appriciated. 

Thanks both. 

[Anonymous]

Hi @Sahir_Maharaj,

The suggestion you've provided wouldn't answer the question in that you're only doing the calculation in the DimX table (The orignal table with lost time). There would be instances where you'd want to filter the lost time aggregation by dimensions in different tables. i.e. DIMA, DIMB, DIMC

[Sahir_Maharaj]

Hello @Anonymous,

Could you please try this approach using a calculated column for grouped aggregation:

DIMX[LostTimeByGroup] = 
CALCULATE(
    SUM(DIMX[Business Duration In Seconds]),
    ALLEXCEPT(DIMX, DIMX[GroupID])
)

Should you require any further assistance, please do not hesitate to reach out to me.