Cells showing blank value in Matrix and effect other data

[goyalsa]

Hi,

I created a date table and linked the "Yearweek" with 2(demand & Supply) different Tables.

The problem I am facing is if demand "Yearweek" or Supply "Yearweek" has no value then the whole week showing "0".

Date table:

Relationship Table:

Report Table:

All the values (Forecasted demand/ Actual Order/ Available Inventory....) calculated via measure.

Measure formula:

CALCULATE(SUM('Actual vs Forecast'[Forecasted Demand]),ALLEXCEPT(Dates,Dates[Loadingweek]),ALLEXCEPT('Actual vs Forecast','Actual vs Forecast'[RegionFrom],'Actual vs Forecast'[CountryFrom],'Actual vs Forecast'[HubFrom],'Actual vs Forecast'[Week Forecast Filled In]))+0

I want that the supply and demand data does not affect one another table and show the value for Supply even if the demand week is 0.

Example: Week 202139 has demand 2 in the excel file but it is showing 0.

Kind regards,

Sahil

[v-xiaotang]

Hi @goyalsa 

Sorry, I can not reproduce this problem and find the reason because of the lack of data for testing, could you share some sample data and expected result?

By the way, do you mean demand of 202139 should be 2?

Best Regards,

Community Support Team _Tang

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

[goyalsa]

Hi @v-xiaotang,

Yes, the demand should be 2.

Another thing that causing problems is because, in Table "TLA", there is no data for loading week 202139.

and that is why whole 202139 is showing 0 value

[goyalsa]

This is affecting because the table "TLA" calculation does not have any data for week 39 (shown in attached ss).

Is it possible to show any loading week even though we do not have the expected availability date?

Loading Status week calculated from Expected available date.

Kind regards,

Sahil

[Anonymous]

@goyalsa 

Using bi-dir relationships with many-to-many... not the greatest idea. Such a model is most likely incorrect but I don't have the full knowledge of it, so it's a mere guess based on my experience. Your fact tables do affect each other precisely because of the bi-dir relationship. You should always strive to use one-way filtering and drop the correct columns from the correct tables on the canvas. Then you'll have much less headache. I would also question the many-to-many relationships but, as I said, I don't have the full knowledge of the problem.

[goyalsa]

Hi @Anonymous,

Thanks for your feedback.  I am trying to use the one-way filtering and still working on it but in case if it is going to be not possible then it's a problem for me.

Still open to hearing others' reviews and ideas on it.

Kind regards,

Sahil