topic Matrix Table in DAX Commands and Tips

Matrix Table

datagoblin77 — Mon, 22 Apr 2024 17:45:54 GMT

Hello,

I am trying to buld a matrix that looks more or less like this.

the Total occupied units is a measure that stems from the count of all the units that have 'Yes' in the Occupied column in the [Fact_units] table. The date are stored in theh [Dim_proj] tables.
Projected move ins and move outs are als o measures in another table that counts move ins and move outs.
The total should be given by the occupied units + move ins - move outs. The next week, however should start with the total from the previous week.

In Excel it's easy because I can just point to a coordinate, which I can't do in DAX.

I managed to do a running total but it doesn't work in a matrix.
So far, I tried with EARLIER, but I can't get it to work because there is no row context I can latch on to.

I tried to create an if statement, but the problem is always the same: I can't get PBI to take the previous row's result as a basis for the next row starting point. Can someone help?

Re: Matrix Table

Anonymous — Tue, 23 Apr 2024 02:03:17 GMT

Hi datagoblin77 ,

I create a table as you mentioned.

Then I create three measures.

What can I do is calculating the total by creating a measure.

total1 = 'Fact_units'[total] + 'Fact_units'[ins] - 'Fact_units'[outs]

So if you can provide me more about your table or details, I will do a further study.

Best Regards

Yilong Zhou

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

Re: Matrix Table

datagoblin77 — Wed, 24 Apr 2024 13:31:02 GMT

Hello Ylong,

Thanks for your reply. te measure you provided is good. The only problem is that the total has to have an IF statement that depends on a measure. The first weeek of the matrix is actually a COUNT of all the units that have a "yes" in the occupied column.

If I were to do the same thing in any other programming language I would use a while loop. In pseudocode it should be something like:
total1= [actual occupied units] + [ins] - [out]
total2= total1 + [ins] - [out]
i= 1
WHILE

counter < MAX(weekcount)
IF weekcount = 1
total1
else
total2
total2 =total2 + ins - out
i ++
This should create a loop that iterates through the weeks. Unfortunately, PBI doesn't have while loops and the variables are static.

Re: Matrix Table

Anonymous — Mon, 29 Apr 2024 08:40:19 GMT

Hi datagoblin77 ,

It's true that there's no DAX code in Desktop that directly replicates the While loop statement as of now, but I think you can check out this topic: For and While Loops in DAX - Microsoft Fabric Community

Best Regards

Yilong Zhou

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