Forum Discussion
Matrix not displaying values
Hello,
I am working on a model I did not create. The model looks at residental unit availability across a university. However, when there are no move-in or move-outs in the week, the matrix I created does not show the number of occupied units. For example on 5/26 and 6/9, I get no values (because that week features no move ins and move outs.
Here are some features of the model.
I have a dim_date table that looks 12 weeks into the future.
The total units are calculated with:
Total Units:=DISTINCTCOUNT(Fact_Units[Unit ID])
The total occupied units are calculated with:
Total Actual Occupied Units:=
CALCULATE(
DISTINCTCOUNT(
Fact_Units[Unit ID]),FILTER(Fact_Units,Fact_Units[Occupancy Flag] = "Yes"),
FILTER(Dim_ProjectionsDate,Dim_ProjectionsDate[Projection Week Ending]<=MAX(Dim_ProjectionsDate[Projection Week Starting])))+0
The week to week projected
Total Projected Move-Ins:= CALCULATE(DISTINCTCOUNT(Fact_Projections[Unit ID]), FILTER(Fact_Projections, Fact_Projections[Move In/Move Out] = "Move-In"))+0
Total Projected Move-Outs:= CALCULATE(DISTINCTCOUNT(Fact_Projections[Unit ID]), FILTER(Fact_Projections, Fact_Projections[Move In/Move Out] = "Move-Out"))+0
Then we calculate the week to week occupancy.
DIM_Total Projected Occupied Units:= IF(AND([Total Projected Move-Ins] = 0, [Total Projected Move-Outs] = 0), BLANK(), CALCULATE([DIM_Week Beginning Occup]+ [Total Projected Move-Ins] - [Total Projected Move-Outs]))
The BLANK() as the true condition is because in a previous iteration of the model, the matrix would arbitrarily put the initial value (total actual occupied units)
I would like to change that BLANK() to the last "viable" value.
Can anyone help?
2 Replies
- lbendlin
Super User
To report on things that are not there you need to use disconnected tables and/or crossjoins
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 Hi datagoblin77
Thanks for the reply from lbendlin .
datagoblin77 , for example, for 6/9, does the last valid value refer to 714?
My suggestion for now is that you get the max date value that is less than the current date and Total Projected Move-Ins and Total Projected Move-Outs are not 0, and then get its valid value.
If the problem persists, as lbendlin said, you need to provide some data so that we can better help you. Please remove any sensitive data in advance.
Best Regards,
Yulia XuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
