Filter for Names that don't appear in the current value selection

[ysf]

Hi

I would like to be able to filter and show names that do not appear for a certain FY.

I have table below.

FY	Name	Department
2021	Mike	Electric
2021	Johan	AI
2021	Jordan	NBA
2021	Ben	Cartoon
2021	Zakes	House
1920	Bella	Space
1920	Wolverine	Cartoon
1920	Blade	Marvel
1920	Clarke	DC
1920	John	Computer
1819	Mike	Electric
1819	Blade	Marvel
1819	Bella	Space

Whenever I select the Year eg (FY=1920) on a filter I expect to see the below names

Not Submitted for FY	Name	Department
1920	Mike	Electric
1920	Johan	AI
1920	Jordan	NBA
1920	Ben	Cartoon
1920	Zakes	House

I have partly achieved this idea but need now to take it a step further by enabling the selected names be filteltered further and give me their respective record so I can see under which Department etc they belong to.

I have used this code to get the below results:

Measure->

No Submissions = CONCATENATEX(FILTER(ALL('Sample'[Name]), NOT[Name] IN VALUES('Sample'[Name])),[Name], " -- ")

Before filtering on the FYBefore filtering on FY

After Filtering on FY

Any assistance would be greatly appreciated.

Regards

[v-zhenbw-msft · Accepted solution]

Hello @ysf ,

Do you want to display the name and department in a table visual?

If so, we can create two measures to meet your needs.

1. We need to create a table that contains only a different name.

Name table = VALUES('Sample'[Name])

2. Then we can create two measures and get the result as well, we need to put the measure in filter on this visual and set it to 0.

Measure = 
var _samplename = VALUES('Sample'[Name])
return
IF(
    MAX('Name table'[Name]) in _samplename,1,0)

Department measure = 
CALCULATE(MAX('Sample'[Department]),FILTER(ALL('Sample'),'Sample'[Name]=MAX('Name table'[Name] )))

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

[v-zhenbw-msft · Accepted solution]

Hello @ysf ,

Do you want to count names that don't appear?

For example, in 1920, it has 5.

If so, it is necessary to create another measure like this,

Count_not_appear =
DISTINCTCOUNT('Name table'[Name]) - DISTINCTCOUNT('Sample'[Name])

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

[v-zhenbw-msft · Accepted solution]

Hello @ysf ,

Do you want to display the name and department in a table visual?

If so, we can create two measures to meet your needs.

1. We need to create a table that contains only a different name.

Name table = VALUES('Sample'[Name])

2. Then we can create two measures and get the result as well, we need to put the measure in filter on this visual and set it to 0.

Measure = 
var _samplename = VALUES('Sample'[Name])
return
IF(
    MAX('Name table'[Name]) in _samplename,1,0)

Department measure = 
CALCULATE(MAX('Sample'[Department]),FILTER(ALL('Sample'),'Sample'[Name]=MAX('Name table'[Name] )))

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

[ysf]

Hi @amitchandak

Thanks this is just what i needed.

Can you show me also how to retrive a count of the names.

Regards

[ysf]

Hi @amitchandak 

Thanks for quick response.

It doesn't return any values. 

Regards

[amitchandak]

@ysf , Try a measure like

calculate(CONCATENATEX(FILTER(values('Sample'[Name]), isblank(calculate(count('Sample'[Department])))), " -- "),all('Sample'))