Forum Discussion

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 co...

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.

Matrix Table.pbix19 KB

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
  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.