Minimizing matrix table

[Lana012021]

Hi

I have matrix table there shows open task by owner and priority, but table going to be quite large, infect there are only showing one task per line.

To try to minimize the table, I manual has added a new column with an index number. Ex. LES have 5 Task in Priority 3, and therefor Task 6 get 1, Task 7 get 2 … and Task 14 get 5. See the table:

I have done that for all task then the matrix table look like this:

But how do I do this automatic with DAX, so I don’t have do the "Id number" manual?

Best regard Lars

[barritown · Accepted solution]

Hi @Lana012021,

If you add an index column so each task has a unique numeric ID, you can use such a calculated column:

Here's the code in plain text:

ID number = 
VAR cOwner = [Owner]
VAR cPriority = [Priority]
VAR cTaskID = [Task ID]
RETURN COUNTROWS ( FILTER ( Data, [Owner] = cOwner && [Priority] = cPriority && [Task ID] <= cTaskID ) )

In your mocking case, the Task ID column can be created like that:

Task ID = INT ( SUBSTITUTE ( [Task name], "Task", "" ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Lana012021]

Thank you very much it was really helpfull!