Creation of a Visual Table based on the original/filtered data

[Anonymous]

I’m developing a PBI dashboard, where I want to create a visual table that is calculated in real time based on another table values that, such like the following example:

My original data is represented on a visual table in PBI, just like this one:

ID	From	To	Category
1	10	20	red
2	20	24	yellow
3	24	30	blue
4	32	40	yellow
5	50	60	red
6	60	65	blue
7	70	80	green
8	90	100	red

The range of data that I want to analyze is from 10 to 100 (From and To).

The visual table that I would like to present, has to analyze each row of the table above and see if there are gaps between the minimum and maximum of the range (10 to 100) by looking to the From and To of each ID, and then calculate the size of the gap. In this case, the result would be this:

From	To	Size
30	32	2
40	50	10
65	70	5
80	90	10

This table has to be created in real time, due to filters that I have that restrict the calculated table. For example, if I selected in the filter that I only wanted category = “blue”, I would have to get the following table:

From	To	Size
10	24	14
30	60	30
65	100	35

The problem that I’m facing is how to create this table that has to analyze each row of the original data / filtered data, and save the values in order to create another table, in real time.

I already tried DAX measures, but due to the filters and the necessity of going row by row, I couldn’t achieve a good solution (my data has 90k rows and some DAX calculations don’t have sufficient memory to do it). Another solution that I tried was python visuals, has I already have the code, but I am facing the problem of creating a table and representing it in PBI. The only library that worked was plotly, but instead of showing the table within the dashboard, it is exporting the desired table to a link in the browser every time I refresh it.

If you have a better idea, I would gladly try new approaches or adapt the ones that I tried.

Thank you!

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

First create a new table:

Then apply the measure:

To =
VAR _a =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[To] )
VAR _b =
    ADDCOLUMNS (
        _a,
        "ToID", MINX ( FILTER ( _a, [ID] > EARLIER ( 'Table'[ID] ) ), [ID] )
    )
VAR _c =
    ADDCOLUMNS (
        _b,
        "ToValue",
            IF (
                ISBLANK ( MAXX ( FILTER ( 'Table', [ID] = EARLIER ( [ToID] ) ), [From] ) ),
                100,
                MAXX ( FILTER ( 'Table', [ID] = EARLIER ( [ToID] ) ), [From] )
            )
    )
VAR _d =
    UNION (
        SELECTCOLUMNS ( _c, "From", [To], "To", [ToValue] ),
        { ( 10, MINX ( 'Table', [From] ) ) }
    )
RETURN
    MAXX (
        FILTER ( _d, [From] <> [To] && [From] = SELECTEDVALUE ( 'From Table'[From] ) ),
        [To]
    )


Size = IF([To]<>BLANK(),[To]-SELECTEDVALUE('From Table'[From]))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.