Measure only works if an unrelated column has data. (Example file Included).

[StuartSmith]

For some reason a measure in a matrix will only show data in the measures column, if there is data present in another column.  As an example (file attached), for Croatia, although they have 1 laptop available and should show in the "Running Total Stock" column for Jan & Feb, it doesnt until March, when there is 1 "Predicted Headcount" entry.  

How can it get it to show the stock in all months?

Find an example pbix file: Example File (Correct one now) 

[MFelix]

Hi @StuartSmith ,

The question on this calculation is that you are picking up the values where the date is before the minimum date.

What is the expected result you want to have?

[StuartSmith]

Sorry, i uploaded the wrong screenshot and file.  Now corrected both.  As you can see for Belgium, in Jan there are 7 Laptops and 4 new starters, leaving 3 laptops.  Then for Feb, there are 3 Laptops and 3 New Starters leaving 0 remainin laptops, and this works through the year.

For Croatia, although there is 1 laptop available, it only shows that 1 laptop once a new starter is present in March.  What it should be showing is Jan: Available 1, Headcount 0, after onboarding 1, then the same for Feb, and then in March, as it displays.

Hope that makes sense

[MFelix]

Hi @StuartSmith ,

Your issue is regading the relationships and the wqay you have your model setup., many-to-many relationship witha filter for both sides can always bring you incorrect results, in this case I have solved making two changes to your model:

• Create a table with coutries
• Change the many to many to many relationship to a many to one to many using the country table has a bridge to your dimensions tables
• Changes the filtering from both to single on the relationship between the calendar and the Dates table
• Change the filed country on your visualizations by the new table values
• Change your measure to :

Running_Total_Stock_After_Onboarding_Measure = 
Var Reuse_and_New_Stock_Count = COUNTROWS('Reuse & New Stock') + 0
Var Total_laptop_Stock = Reuse_and_New_Stock_Count
Var Headcount_Running_Total = CALCULATE (COUNTROWS ( 'HR Data' ),FILTER ( ALLSELECTED ( Dates ), Dates[Date] < MIN( Dates[Date] ) ))
Return
 Total_Laptop_Stock - Headcount_Running_Total +0

Be aware that in this measure I have replaces the MIN that was getting the value from the HR table to the Dates table (since this is the table used on the visualization and also on the one side of the relatioship.

Final result below and in attach PBIX file:

Check this video of Marco Russo with explanation about the many to many relationship.

https://www.youtube.com/watch?v=wRSJ6TYjEu0

[StuartSmith]

Thanks, that seems to have done the trick.  I then tried to create 4 yearly quarter tabs for ...

Jan, Feb, Mar

Apr, May, June

July, Aug, Sept

Oct, Nov, Dec

and create a page filter for each page with the above months, hoping it would provide a running total split into quarters, but the "Available Laptop Stock" value resets for each tab and shows, as an example of Belgium "7", when at the end of Q1 the laptop count was -11, and therefore Q2 April should be -11, then July should be -32, etc.  But they all show 7.   The other 2 columns work fine.

I am tryng to figure this out, but my test measures have about 10 lines of code, where I can getting the headcount for all the previous months, and trying to perform some from a calculation, but im sure there us a more effecient way.

Hope that makes sense and have added an update file with the quarter tabs.

Thanks in advance,

RMC_Good_1 with Quarter Tabs 

[MFelix]

Hi @StuartSmith ,

Has refered by @v-easonf-msft if you replace the ALLSELECTED by ALL then you get the expected result.

Q3

[StuartSmith]

Thanks Both, I had tried that and the calculation works fine until you get a "Predicted Headcount" value of "0" and then the measure breaks. This happens on the example file and my working file.  As an example Belgium works fine until Aug and then when there is 0 "Predicted Headcount", instead of "Stock After Onboarding" having the value of "-35", it has "7" and the same for Croatia, "Stock after Onboarding" should be "-2", but showing "1"?  

Any ideas? Thanks in advance.

[StuartSmith]

And if I add an entry for Aug for Belgium, it corrects the error, but if the headcount is 0, the measure breaks. 

[MFelix · Accepted solution]

Hi @StuartSmith ,

Try to remake you measure for the stock after to :

Stock_After_Onboarding_Measure V2 = 
[Running_Total_Stock_After_Onboarding_Measure]   - [Predicted_Headcount_Measure]

Result attach:

[StuartSmith]

Thanks for all your help with this and now everything is working perfectly.

[v-easonf-msft]

Hi, @StuartSmith 

If I understand correctly, you can try to use 'All' to  replace 'Allselected' in measure 'Running_Total_Stock_After_Onboarding_Measure'.

Running_Total_Stock_After_Onboarding_Measure = 
Var Reuse_and_New_Stock_Count = COUNTROWS('Reuse & New Stock') + 0
Var Total_laptop_Stock = Reuse_and_New_Stock_Count
Var Headcount_Running_Total = CALCULATE (COUNTROWS ( 'HR Data' ),FILTER ( ALL( Dates ), Dates[Date] < MIN( Dates[Date] ) ))
Return
 Total_Laptop_Stock - Headcount_Running_Total +0

Best Regards,
Community Support Team _ Eason

[StuartSmith]

Thanks, thats great and much appreciated that you took the time to help.  I'm just going through your alterations now on the actual report and will then double check the results, but on initial inspection, it all looks good.  I must admit I had used a "Bridge" table before, but had forgot about it. Will accept solution once tested.