How to introduce the results of a user-selected measure in a table to perform aggregations

[arielcedola]

Hi,

I need help to aggregate results of a measure that must be chosen by the user.

I have a table (DataDays) with data about certain processes performed by some companies. These processes have 6 phases, and the duration of each phase in days is registered in the columns D1 to D6. The start and end dates of each phase are denominated A, B, C, D, E, F, G: A and B are the start and end dates of phase 1, respectively, B and C are those for phase 2, and so on. The goal is to allow the user to analyze the duration of subsets of the 6 phases, by choosing the start and end dates of the desired subset, and then grouping the duration in three categories and aggregating the results.

For instance the phases between date C and F constitute the subset of phases 3-4-5. For each process in the table the duration category will be 1, 2 or 3 according to the amount of days of the phases 3-4-5. At the end the count of processes with categories 1, 2 and 3 must be shown in a pie chart.

Here an image of the table:

The way I implemented the customer selection of the subset of phases is through 2 measures with SWITCH functions (Measure From and Measure To), that call one out of 6 other measures (M1 to M6) as a function of the dates selected by the customer in 2 slicers, one for the start date (Date1: A to F) and the other for the end date (Date2: B to G). Then a 3rd measure (From To) performs the substraction between the results of the above 2 measures, giving the amount of days, and a last measure (Category) determines the category 1, 2 or 3.

I think I should construct a DAX table in order to be able to perform the final aggregation of the categories. I tried, but it is not working. I create the DAX table in this way:

Table with measures = ADDCOLUMNS(DataDays; "M1"; [M1]; "M2"; [M2]; "M3"; [M3]; "M4"; [M4]; "M5"; [M5]; "M6"; [M6]; "Category"; [Category])

But I get the error "MdxScript(Model): A table of multiple values was supplied where a single value was expected."

Have a look to my measures:

M1 = MAX(DataDays[D1]) + MAX(DataDays[D2]) + MAX(DataDays[D3]) + MAX(DataDays[D4]) + MAX(DataDays[D5]) + MAX(DataDays[D6])

M2 = MAX(DataDays[D2]) + MAX(DataDays[D3]) + MAX(DataDays[D4]) + MAX(DataDays[D5]) + MAX(DataDays[D6])

...

M6 = MAX(DataDays[D6])

Measure From = SWITCH(TRUE(); VALUES('From'[Date1]) = "A"; [M1]; VALUES('From'[Date1]) = "B"; [M2]; VALUES('From'[Date1]) = "C"; [M3]; VALUES('From'[Date1]) = "D"; [M4]; VALUES('From'[Date1]) = "E"; [M5]; VALUES('From'[Date1]) = "F"; [M6]; 0)

Measure To = SWITCH(TRUE(); VALUES('To'[Date2]) = "B"; [M2]; VALUES('To'[Date2]) = "C"; [M3]; VALUES('To'[Date2]) = "D"; [M4]; VALUES('To'[Date2]) = "E"; [M5]; VALUES('To'[Date2]) = "F"; [M6]; VALUES('To'[Date2]) = "G"; 0; BLANK())

From To = [Measure From] - [Measure To]

Category = IF([From To] <= 30; 1; IF([From To] > 40; 3; 2))

Is there an alternative way to write the measures, in order to have them working correctly in the DAX table? Otherwise, is there any other way to get what I need?

Belos find the link to the pbix file. Thanks!

https://drive.google.com/open?id=12Med3J60Pf5lUsJvmjAMeeUAaOgJehv7

[Anonymous]

Values in model tables are STATIC. You cannot have dynamic columns or dynamic values in columns.

Best
D

[arielcedola]

@Anonymous thanks, do you have some suggestion about how to aggregate the results of a measure applied in the way I explained?

On the other hand, I am being able to get the table that I need but in a visual table (as shown in the original post), is there any way to aggregate the values that are in a column of a visual table? In my case, these values are in the column named Category.

Thanks

[sturlaws · Accepted solution]

Hi @arielcedola,

your measures are ok as they are, but you can reduce the number of measures by doing something like this:

NumberOfDays =
VAR _from =
    CALCULATE ( SELECTEDVALUE ( 'From'[Date1] ) )
VAR _to =
    CALCULATE ( SELECTEDVALUE ( 'To'[Date2] ) )
VAR _m1 =
    IF ( _from = "A" && _to > "A"; 1; 0 )
VAR _m2 =
    IF ( _from <= "B" && _to > "B"; 1; 0 )
VAR _m3 =
    IF ( _from <= "C" && _to > "C"; 1; 0 )
VAR _m4 =
    IF ( _from <= "D" && _to > "D"; 1; 0 )
VAR _m5 =
    IF ( _from <= "E" && _to > "E"; 1; 0 )
VAR _m6 =
    IF ( _from <= "F" && _to > "F"; 1; 0 )
RETURN
    SUMX (
        VALUES ( DataDays[ProcessID] );
        SUM ( DataDays[d1] ) * _m1
            + SUM ( DataDays[d2] ) * _m2
            + SUM ( DataDays[d3] ) * _m3
            + SUM ( DataDays[d4] ) * _m4
            + SUM ( DataDays[d5] ) * _m5
            + SUM ( DataDays[d6] ) * _m6
    )

As mentioned, if you add a measure as a calcultaed column, it does not respond to filter selections. The workaround for this is to create a new table with the category values. You can then create a measure like this:

CountPrCategory =
VAR _currentCategory =
    CALCULATE ( SELECTEDVALUE ( Category[Category] ) )
RETURN
    COUNTROWS (
        FILTER (
            ADDCOLUMNS ( VALUES ( DataDays[ProcessID] ); "cat"; [Category] );
            [cat] = _currentCategory
        )
    )

 In this measure Category is the new table. Create your pie chart with Category[Category] on the axis, and [CountPrCategory] as value.

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Now you create

[arielcedola]

Hi @sturlaws 

this is exactly what I needed, a quick response, simple, very well explained and right to the point.

Thank you so much for your time man!

Best,

Ariel