Group DAX measures in matrix by Category and Subcategory

[vpe27]

Hi,

Need your help, how to group DAX measures in matrix visual by non existing category and subcategory. I read probably all the similar posts in the forums, and try some things, unfortunately no success.

Situation:

1. I have simple model, only one FactTable and one dimDate:

2. Data in FactTable like below:

ID	Date	Amount
CU820	2023-01-01	21,27
CU820	2023-01-02	21,27
CU820	2023-01-31	21,38
CU820	2023-02-05	21,41
CU820	2023-02-17	21,33
CU820	2023-02-28	21,27
CU820	2023-03-01	21,28
CU820	2023-03-15	21,77
CU820	2023-03-27	21,27
50CQ9	2023-01-01	304,33
50CQ9	2023-01-10	303,99
50CQ9	2023-01-31	303,87
50CQ9	2023-02-05	304,01
50CQ9	2023-02-17	304,09
50CQ9	2023-02-28	304,09
50CQ9	2023-03-01	303,87
50CQ9	2023-03-15	304,09
50CQ9	2023-03-27	303,99
40CQ9	2023-01-01	45,66
40CQ9	2023-01-10	44,34
40CQ9	2023-01-31	45,76
40CQ9	2023-02-05	45,66
40CQ9	2023-02-17	45,76
40CQ9	2023-02-28	44,34
40CQ9	2023-03-01	45,76
40CQ9	2023-03-15	45,66
40CQ9	2023-03-27	45,76
20CQ9	2023-01-01	132,29
20CQ9	2023-01-02	133,01
20CQ9	2023-01-31	132,87
20CQ9	2023-02-05	132,29
20CQ9	2023-02-17	133,01
20CQ9	2023-02-28	132,87
20CQ9	2023-03-01	132,29
20CQ9	2023-03-15	132,87
20CQ9	2023-03-27	132,87
CE301	2023-01-01	68,44
CE301	2023-01-02	68,67
CE301	2023-01-10	65,44
CE301	2023-02-05	68,67
CE301	2023-02-17	65,44
CE301	2023-02-28	68,44
CE301	2023-03-01	68,67
CE301	2023-03-15	65,44
CE301	2023-03-27	65,44
1XQ90	2023-01-01	2099,11
1XQ90	2023-01-02	2100,22
1XQ90	2023-01-31	2099,11
1XQ90	2023-02-05	2099,11
1XQ90	2023-02-17	2103,44
1XQ90	2023-02-28	2100,22
1XQ90	2023-03-01	2103,44
1XQ90	2023-03-15	2100,22
1XQ90	2023-03-27	2103,44
CQ911	2023-01-01	500,99
CQ911	2023-01-02	501,56
CQ911	2023-01-31	501,44
CQ911	2023-02-05	501,56
CQ911	2023-02-17	500,99
CQ911	2023-02-28	502,01
CQ911	2023-03-01	501,56
CQ911	2023-03-15	502,01
CQ911	2023-03-27	500,99

3. Prepared few simple DAX measures of these ID's, ex. AVERAGEX, SUMX, CALCULATE, etc.

4. Visualized in Matrix these DAX measures as Rows and Year&Month as Columns.

Task:

Additionally I need to group measure rows by Category and Subcategory, and matrix should look like this:

Please note:

1. Category and Subcategory doesn't exist in any table.

2. Some Categories can have few Subcategories.

3. Some IDs from FactTable on which Measures are made may belong to multiple Categories or Subcategories.

4. Nead possibility of +/- by Category and Subcategory.

Thanks in advance,

vpe27

[Ashish_Mathur]

Hi,

If Categories and Sub categories are not present in any Table, then how do you propose to show those columns/fields in your visual????

[vpe27]

Hello,

Yes, you are right 🙂 But this factor also important.

I can create separate table with Categories and Subcategories, like this example:

DynamicMeasuresTable = DATATABLE(
"Measure", STRING,
"Sort Order", INTEGER,
"Category", STRING,
"Subcategory", STRING,
{
{"Device CU820 Working Days", 1, "Load", ""},
{"Device CU820 Average load, %", 2, "Load", ""},
{"Device 50CQ9 Heat", 4, "Heat and Electricity", "Heat"},
{"Device 40CQ9 Heat", 5, "Heat and Electricity", "Heat"}

/* Other Measures with Categories and Subcategories -> {...}
}
)

Then create Dynamic Measure to map Categories / Subcategories with real Measures:

DynamicMeasure =
VAR selection = SELECTEDVALUE(DynamicMeasures[Measure])
RETURN
SWITCH(
selection,
"Device CU820 Working Days", [010_DeviceCU820WorkingDays],
"Device CU820 Average load", [020_DeviceCU820AverageLoad],
"Device 50CQ9 Heat", [040_Device50CQ9Heat],
"Device 40CQ9 Hea", [050_Device40CQ9Heat],

/* Other Measures with Categories/Subcategories map
BLANK()
)

But here is the problem, how to relate with main FactTable and dimDate?

Another solution instead this one is do everything with Tabular Editor, create Dynamic Measures. Bus still, I face same issue.

Regards,

vpe27