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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
scaast
Frequent Visitor

Dual Filters Problem

Hello,  I have a table with Date, Employee Name, Score, and a Survey column.   I want to display the bottom ten Employees that received at least 10 surveys in a selected time period.   The Survey column is always 1...ie Alvin received a second survey on 8/7, and the survey column still reflects a one.  I'm a newb with DAX, and I've not been able to come up with the right syntax to make it happen.  All help is appreciated.  

 

EXAMPLE:

 

DateEmployee NameScoreSurvey
8/3/2017Alvin41
8/3/2017Theodore101
8/5/2017Simon71
8/7/2017Alvin81
1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @scaast,

 

In my test, the table visual displayed the bottom 2 Employees that received at least 3 surveys in a selected time period.

 

Please refer to measures:

Survey =
CALCULATE (
    COUNT ( 'Employee Survery'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Employee Survery' ),
        'Employee Survery'[Employee Name]
            = SELECTEDVALUE ( 'Employee Survery'[Employee Name] )
    )
)

Rank =
IF (
    'Employee Survery'[Survey] >= 3,
    RANKX ( ALLSELECTED ( 'Employee Survery' ), [Survey],, DESC, DENSE ),
    BLANK ()
)

flag1 = MAXX(ALLSELECTED('Employee Survery'),[Rank])
flag2 = IF([Rank]>=[flag1]-1&&[Rank]<>BLANK(),1,0)

Add measure [flag2] to visual level filter, and set its value to 1.

3.PNG

1.PNG

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Microsoft Employee
Microsoft Employee

Hi @scaast,

 

In my test, the table visual displayed the bottom 2 Employees that received at least 3 surveys in a selected time period.

 

Please refer to measures:

Survey =
CALCULATE (
    COUNT ( 'Employee Survery'[Employee Name] ),
    FILTER (
        ALLSELECTED ( 'Employee Survery' ),
        'Employee Survery'[Employee Name]
            = SELECTEDVALUE ( 'Employee Survery'[Employee Name] )
    )
)

Rank =
IF (
    'Employee Survery'[Survey] >= 3,
    RANKX ( ALLSELECTED ( 'Employee Survery' ), [Survey],, DESC, DENSE ),
    BLANK ()
)

flag1 = MAXX(ALLSELECTED('Employee Survery'),[Rank])
flag2 = IF([Rank]>=[flag1]-1&&[Rank]<>BLANK(),1,0)

Add measure [flag2] to visual level filter, and set its value to 1.

3.PNG

1.PNG

2.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Very helpful.   Thank you!  

Seward12533
Solution Sage
Solution Sage

Need give us a bit more to work with. Can you share how your table is built, what your data model looks like and any measures.  If your using filters in your measure for Survey count and score you probably don't need them and should let PowerBI imply the filter context as it builds you table. I woudl think your measures shoudl simply be

 

Score = AVERAGE(table[score])

Survey = DISTINCTCOUNT(table[surveyid])

 

If you then build a Matrix visual with Date, Employee Name and the measures it will display one line per employee wiht average score and # of surveys. if you want one row per survey include Survey ID in the rows. 

 

Tips: create a survey rank measure using RANKX with the decending option to identify the bottom n and then filter you visual with that measure. 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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