Count Measure Results in Summarized Table in Matrix Visual

[ct_tm]

Hello,

I am working on a decently complex report page that contains a matrix that evaluates a score by Vice President and subordinate Manager. To save compute time for the end user I took most of the long computing measures and created pre-computed tables that refresh when the report refreshes each morning. There are 5 tables of precomputed data and for the purpose of this question the final score/rating is the resulting sum of 5 ratings measures (one for each precomputed table). The resulting measure provides a total score and that score is then evaluated against a spread to produce a letter rating (A,B,C,D,E). This dataset was built originally as SCD0 (no historical dimension tracking) but is now setup as an SCD type 2 where we are tracking history of manager and subordinate as well as historical tracking of properties they manage. To handle this I use a transition table that handles all the person/property/datetime relationships to facilitate the SCD2 setup (named tblRollupKey). 

Up until now I precomputed the Total table because that was the easiest way to be able to do a simple countrows on the rating because I could produce a column of "letter" data like so:

The problem is that now that I have transitioned to an SCD2 model the precomputed total table doesn't work correctly because basing the total evaluation off of only the property and month does not allow for the recomputing that occurs when filtering in other widgets because the measures that read from the other 5 precomputed tables get recomputed due to the time/date factors of having an SCD2 model. The total table does work for the highest level, but once we start trying to figure out who was where, when, and start redoing the math to compute the different subtotal values things start mismatching and a rating of a "C" at the total level can become a different letter once you factor in other historical dimensions

So, this matrix will have to be built dynamically instead of a hard table being created, and I struggle with the summarize/addcolumns processes. 

I created a table with no relationships that contain the distinct resulting letter grades (A,B,C,D,E) called "FOCUS Scoring" and used the "Total Rating" column in this disconnected table as the "column" value in the matrix. But when I try to write a measure to summarize and group the results into these values I either get the same number across all the columns or I get an error depending on what I change.

Here is my current measure, but all it does is produce a 1 in each Ratings columns. When I create a table to check whether the table in the variable works, I see the results I would expect to see but I just can't figure out how to virtually count these rows and return the correct result back to the matrix visual so that they fall under the resulting letter grade. Also, for the purpose of this matrix we only care about the latest selected month from the date slicer.

Count Ratings = 
VAR table1 = 
CALCULATETABLE(
    SUMMARIZE(
        tblRollupKey,
        tblRollupKey[propMonthKey],
        "Rating",
        [Total FOCUS Rating]
    ),
    FILTER('Calendar', 'Calendar'[MonthYearNum] = MAX('Calendar'[MonthYearNum]))
)
RETURN
CALCULATE(
    COUNTROWS(table1),
    FILTER(table1, [Rating] in VALUES('FOCUS Scoring'[Total Rating]))
)

Thank you, and let me know if I can provide anything else.

[Anonymous]

Hi @ct_tm 

Can you provide some sample data so that can provide more suggestion for you?

Best Regards!

Yolo Zhu

[ct_tm]

@Anonymous thank you for your interest!

When I was writing up the original post I was thinking about how I could supply some sample data but the model is just so large and complex that I couldnt think of a reasonable way to do it. For example, the measure [Total FOCUS Rating] is the sum total of total lines from 5 different tables that are precomputed at refresh time.

So, what part of this would you like me to try to create data for? 

If it helps here is the relationional diagram for this calculation.

Red are precomputed tables that each contain a final total score/rating by property by month.

Blue is the table that contains all combinations of property and month.

Green is the dimension table that contains the effective dates of management by property, vice president, and manager. All three of these values have significant impact on what data is being computed.

Also, like i said in an update, the second code with the SUMX is functioning now but it is running very slow and the row total shows 0 instead of counting the sum of the letter grades for each individual person.

[ct_tm]

As an update I came across a question in a different forum that was able to solve a portion of the problem, while creating a new one. 

powerbi - DAX grouping by a measure result - Stack Overflow

This now calculates the columns correctly (based on SCD2 history) but creates a problem at the row total level where now the row totals are 0. Probably has to do with the IF and SELECTEDVALUE. 

Another issue is that this takes forever to calculate.

So, based on the link above and the code below how can I optimize this to produce a faster query with proper row totaling?

Count Ratings = 
VAR table1 = 
CALCULATETABLE(
    SUMMARIZE(
        tblRollupKey,
        tblRollupKey[propMonthKey],
        "Rating",
        [Total FOCUS Rating]
    ),
    FILTER('Calendar', 'Calendar'[MonthYearNum] = MAX('Calendar'[MonthYearNum]))
)
RETURN
SUMX(table1, IF([Rating] = SELECTEDVALUE('FOCUS Scoring'[Total Rating]), 1, 0))