Creating a New Table in Data Model analogous to a Matrix in the Visual

[Anonymous]

Hi,

Can anyone suggest how to create a table in the data model similar to the matrix highlighted below? I am facing issue when I am applying the measure to thousands of employee's and the visual is running out of memory in the worksplace. I want to pre-load the data in the matrix to a table so that the performance improves.

@Anonymous helped with the measure below. Now I need to create a table replicate the matrix highlighted above to a table so that I do not run into performance issue after publishing the report.

Who can sell product? =

VAR _pslist =

CALCULATETABLE (

VALUES ( 'Product'[Pskill] ),

FILTER ( 'Product', 'Product'[Product] = SELECTEDVALUE ( 'Product'[Product] ) )

)

VAR _pscount =

COUNTROWS ( _pslist )

VAR _escount =

CALCULATE (

DISTINCTCOUNT ( 'Employee'[Eskill] ),

FILTER (

'Employee',

'Employee'[Employee] = SELECTEDVALUE ( 'Employee'[Employee] )

&& 'Employee'[Eskill] IN _pslist

)

)

RETURN

IF ( _pscount = _escount, "Y", BLANK () )

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can use the following calculated table:

Table =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'Product'[Product] ), VALUES ( 'Employee'[Employee] ) ),
    "SKILL MATCH",
        VAR _pslist =
            CALCULATETABLE (
                VALUES ( 'Product'[Pskill] ),
                FILTER ( 'Product', 'Product'[Product] = EARLIER ( 'Product'[Product] ) )
            )
        VAR _pscount =
            COUNTROWS ( _pslist )
        VAR _escount =
            CALCULATE (
                DISTINCTCOUNT ( 'Employee'[Eskill] ),
                FILTER (
                    'Employee',
                    'Employee'[Employee] = EARLIER ( 'Employee'[Employee] )
                        && 'Employee'[Eskill] IN _pslist
                )
            )
        RETURN
            IF ( _pscount = _escount, "Y", BLANK () )
)

But it will also cause the performance issue when data refresh. The root solution for it is to apply the translation in data source.

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

Best Regards,

Dedmon Dai