Slice long dataset with "AND" condition

[Anonymous]

Hi!

I have a long dataset (columns="Name", "Skill", "Grade" and >10000 rows).

Columns description:

Name: Unique person identifier.

Skill: Elements such as "French", "ML", "Accounting" out of a list of more than 100 elements.

Grade: How good a preson is at that skill.

I woud like to slice the dataset in order to find all the people with skills above given grades. So for example for this dataset:

Name	Skill	Grade
James	French	2
James	ML	8
Marc	ML	6
Marc	English	10
Marc	French	9

I want to be able to filter for French with grade >5 and ML with grade >4 to obtain that Marc satisfy these conditions.

Beacuse of the many skills (more than 100) pivoting is not an option as I would have to build a slicer for more than 100 columns.

I really appreciate your help!

Best,

S.

[PaulDBrown · Accepted solution]

Here is another way.

First create  2 disconnected tables for skill and 2 disconnected tables for grades (for the latter I've created  tables from 0 to 10) to use as slicers
The model looks like this:

Next create the following measures to use as a filter in the filter pane:

This Filter Name Measure is to be used as a TopN filter on the Name field (Top 1)

Filter Name =
VAR Sel1 =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        FILTER (
            'Table',
            'Table'[Skill] = SELECTEDVALUE ( 'Skills Selection 1'[Skill 1] )
                && 'Table'[Grade] > SELECTEDVALUE ( 'Grade 1'[Grade 1] )
        )
    )
VAR Sel2 =
    CALCULATETABLE (
        VALUES ( 'Table'[Name] ),
        FILTER (
            'Table',
            'Table'[Skill] = SELECTEDVALUE ( 'Skills Selection 2'[Skill 2] )
                && 'Table'[Grade] > SELECTEDVALUE ( 'Grade 2'[Grade 2] )
        )
    )
VAR _list =
    INTERSECT ( Sel1, Sel2 )
RETURN
    COUNTROWS ( INTERSECT ( VALUES ( 'Table'[Name] ), _list ) )

Filter Subjects =
VAR Sel1 =
    VALUES ( 'Skills Selection 1'[Skill 1] )
VAR Sel2 =
    VALUES ( 'Skills Selection 2'[Skill 2] )
VAR _list =
    UNION ( Sel1, Sel2 )
RETURN
    COUNTROWS ( INTERSECT ( VALUES ( 'Table'[Skill] ), _list ) )

To get:

I've attached the sample PBIX file

[Anonymous]

@Anonymous , you can achieve this by creating two measures:

Max French Grade = 
    MAXX(
       FILTER('YourTable', 'YourTable'[Skill] = "French"),
        'YourTable'[Grade]
    )

Max ML Grade = 
    MAXX(
       FILTER('YourTable', 'YourTable'[Skill] = "ML"),
        'YourTable'[Grade]
    )

and create a table visual with three columns: Name, Max ML Grade, Max French Grade. This gives you

then on the table filters, set Max French Grade to Is Greater than 5, and set the Max ML Grade to Is Greater Than 4. This gives Marc as the only name in the list:

Another way is to create a measure that applies the both the French and ML logic and creates a flag for the Name:

French>5 AND ML>4 = 
    MAXX(
        SUMMARIZE(
            'YourTable',
            'YourTable'[Name],
            "_Flag", 
                VAR vA = [Max French Grade]
                RETURN
                    CALCULATE(
                        IF([Max French Grade] > 5 && [Max ML Grade] > 4, "Y", "N"),
                        ALL('YourTable'[Grade], 'YourTable'[Skill])
                    )
        )
        ,[_Flag]
    )

This measure can be dropped onto the table, along with the Name, Skill, and Grade columns

and if you wanted, you can filter that measure for values of Y, which will display: