topic Re: Model and DAX for Volume in DAX Commands and Tips

Model and DAX for Volume

ssysanny — Sat, 15 Oct 2022 00:31:15 GMT

planned to create one model and calculate something as attached.

we have production volume in table tblproductvol for every month of year.

and also one table named tblproducing to use this volume to compute some total cost or other usage or unit price etc. but now it is not good to get the right production volume for this table.

either some prompts like not create relationship or the production volume is not right for detailed type like actual or budget.

any master can help on this? 1, how to get the right production volume? 2, how to create the right relationship model? thanks.

https://1drv.ms/x/s!AqYSh-5UaI9Wg2FRJL-tayY2BcjJ?e=aFW6hh

Re: Model and DAX for Volume

Anonymous — Mon, 17 Oct 2022 06:32:56 GMT

Hi ssysanny ,

According to your statement, I think you want to know how to create relationship between two tables. Here I suggest you to build relationships based on the keycolumns between tables like ID.

For reference:

Model relationships in Power BI Desktop

Create and manage relationships in Power BI Desktop

I have checked your attatch file, I am still confused about your requirement. I can see multiple sheets in your file, and which one is your underlying data and which one is your requirement?

Please show me more details about your requirement. Or you can show me a screenshot with the result you want.

Best Regards,
Rico Zhou

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

Re: Model and DAX for Volume

ssysanny — Tue, 18 Oct 2022 22:09:09 GMT

many thanks to you.

table : tblproducing is a fact table including all of actual data and other desired data like budget or previous years data to compare and analyze.

table tblproductVol is a also fact table including production volume. what these 2 tables common point is all include produciton line.

tblproduction table provide summary production volume by different production lines by period. however tblproducing need these production volume by production lines and by periods to calculate total cost and others.

but now couldn't get the right volume flexiblly by production line and periods via measure: MonthVolM, we could see that in sheet "Data" range K24:S32, so when using Measure :monthvolm to calculate , couldn't get the expected result.

Type	Production line	Month	right	wrong
act	Gline1	Jan	570	875
Act	Gline2	Jan	351	548
act	GHP	Jan	273	417

Re: Model and DAX for Volume

ssysanny — Tue, 18 Oct 2022 22:26:36 GMT

and tblproducing is part of fact data . and if put all of these production volume by production lines by period into tblproducing table, these will be many duplicated data in it. so question is :

1, how to get right produciton volume by production lines and periods rightly when to create measure from tblproducing table?

2, or establish one right or proper relationship to improve these? because couldn't use tblproductvol table some columns and tblproducing table together to calculate or present as there is prompt appeared.