Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
blake_leblanc
Advocate I
Advocate I

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

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)

 

topn_count_example.png

 

 

 

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:0015568.738
1/1/2019 0:00269891.03
1/1/2019 0:00312807.08
1/1/2019 0:00468851.35
1/1/2019 0:0052755.848
1/1/2019 0:00678801.34
1/1/2019 0:00767741.83
1/1/2019 0:00843061.47
1/1/2019 0:00980554.17
1/1/2019 0:001077910.99
1/1/2019 0:001154467.13
1/1/2019 0:001246198.4
1/1/2019 0:001349248.13
1/1/2019 0:001476489.75
1/1/2019 0:00151495.989
1/1/2019 0:001667858.69
1/1/2019 0:001710028
1/1/2019 0:001844961.23
1/1/2019 0:001974369.47
1/1/2019 0:002060877
1/1/2019 0:002115663.33
1/1/2019 0:002236276.2
1/1/2019 0:002336913.25
1/1/2019 0:002422711.02
2 REPLIES 2
amitchandak
Super User
Super User

@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


@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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors