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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Slice long dataset with "AND" condition

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:

NameSkillGrade
JamesFrench2
JamesML8
MarcML6
MarcEnglish10
MarcFrench9

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.

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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:

Model.png

 

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:

Grades.gif

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

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:

Model.png

 

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:

Grades.gif

 

I've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@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

EylesIT_0-1656004181097.png

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:

EylesIT_1-1656004355849.png

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

EylesIT_2-1656005180318.png

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

EylesIT_3-1656005231366.png

 

 

 

 

 

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.