Forum Discussion

datagoblin77's avatar
datagoblin77
Frequent Visitor
2 years ago

Matrix Table

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?

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • datagoblin77's avatar
      datagoblin77
      Frequent Visitor
      2 years ago

      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. 


      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.