How to "apply" results from a virtual table to a measure, for a visual?

[blake_leblanc]

I created a virtual table just to "check" that I can achieve the results I'm after. What I'm stuck at now, is how to then USE that equivalent virtual table within a measure, to be executed across the "full" timescale. 

So ideally, as it's iterating through the table, it would associate to the evaluation context offered by the matrix visual.

I suspect implementing a RANKX rather than a TopN could be helpful too, that's one of the other avenues I'm exploring...

Thanks in advance for any tips or suggestions! I will update if/when I have a breakthrough on my end as well.

(I apologize in advance for having to obscure things, but it's all I have authority to share at this time)

Table = 
VAR __Year =
    MAX( DIM_Dates[Year] )
VAR __Month = 
    MAX( DIM_Dates[Month] )
VAR __Hour =
    MAX( DIM_Hours[Hour] )
VAR __Market = 
    MAX( DIM_MarketRegions[Market] )
VAR SystemTable =
    FILTER(
        ADDCOLUMNS(
            SUMMARIZECOLUMNS(
                DIM_Dates[Year],
                DIM_Dates[Month],
                DIM_Dates[Day],
                DIM_Hours[Hour],
                DIM_MarketRegions[Market]
            ),
            "@value", [System value]
        ),
        [Year] = 2019
            && [Month] = 6
            && [Market] = "Name"
    )
VAR TopNTable = 
    TOPN(
        5,
        SystemTable,
        [@value],
        DESC
    )
VAR Result = 
    TopNTable
RETURN
    Result

 And here is the added column that takes care of the count

CountRowsFiltered = 
VAR __Hour =
    [Hour]
VAR Result =
CALCULATE(
    COUNTROWS(
        'Table'
    ),
    FILTER(
        'Table',
        [Hour] = __Hour
    )
)
RETURN
    Result

I've tried attaching some sample data as a *.xlsx, *.csv, and *.txt but it says those file types are not supported... 

Here is an example of what a single day looks like

1/1/2019 0:00	1	5568.738
1/1/2019 0:00	2	69891.03
1/1/2019 0:00	3	12807.08
1/1/2019 0:00	4	68851.35
1/1/2019 0:00	5	2755.848
1/1/2019 0:00	6	78801.34
1/1/2019 0:00	7	67741.83
1/1/2019 0:00	8	43061.47
1/1/2019 0:00	9	80554.17
1/1/2019 0:00	10	77910.99
1/1/2019 0:00	11	54467.13
1/1/2019 0:00	12	46198.4
1/1/2019 0:00	13	49248.13
1/1/2019 0:00	14	76489.75
1/1/2019 0:00	15	1495.989
1/1/2019 0:00	16	67858.69
1/1/2019 0:00	17	10028
1/1/2019 0:00	18	44961.23
1/1/2019 0:00	19	74369.47
1/1/2019 0:00	20	60877
1/1/2019 0:00	21	15663.33
1/1/2019 0:00	22	36276.2
1/1/2019 0:00	23	36913.25
1/1/2019 0:00	24	22711.02

[amitchandak]

@blake_leblanc , A calculated table can not table slicer value, so no benefit of using a measure there.

You have to create the table as var in measure, then it will take slicer values

[blake_leblanc]

---

@amitchandak wrote:

@blake_leblanc, A calculated table can not table slicer value, so no benefit of using a measure there.

You have to create the table as var in measure, then it will take slicer values

---

Hello @amitchandak ! Yes, you're absolutely right! Sorry, I should have been more clear in my initial post.

That's the part I'm having a hard time understanding. Now that I know I *can* create such a table of results (by testing in a calculated table), how do I apply that same understanding within a measure?

The measure return is essentially a `COUNTAX()` of what table? Of the `TopNTable` generated within the measure (like you're saying). But that table needs to have an evaulation context that "knows" what Year/Month it is currently on within the visual.

(I know that's very open-ended, I'm just talking out loud a bit). I'll take another try at it later today and update with any progress!