Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
datagoblin77
Frequent Visitor

Matrix Table

Hello, excle example.png

 

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 3
v-yilong-msft
Community Support
Community Support

Hi @datagoblin77 ,

I create a table as you mentioned.

vyilongmsft_0-1713837463453.png

Then I create three measures.

vyilongmsft_1-1713837521620.png

vyilongmsft_2-1713837535759.png

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

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

vyilongmsft_3-1713837699717.png

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.

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. 


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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.