We need distinct ID list of below explained comments (Below Mentioned):

[smpowerbi]

Hi Team,

I have done the calculation based on below Mentioned file. But, We need only thing as "How to get the remaining of distinct Id's except filter selection".

Now i used Swtich table values "Plan Yes/NO"

If Plan "Yes" : Out of 100% (Got the lists of Id. Ref : Below Mentioned)

If Plan "No" : 1 - 100% (We need same as above of Plan "Yes")

Table1:

Id	year	Level	Customer	weekname
1	2019	WK4	ABC	week1
2	2019	WK4	ABC	week1
3	2019	WK4	ABC	week1
4	2019	WK4	ABC	week1
5	2019	WK4	ABC	week1
6	2019	WK4	ABC	week2
1	2019	WK4	ABC	week2
2	2019	WK4	ABC	week2
3	2019	WK4	ABC	week2
4	2019	WK4	ABC	week2
7	2019	WK4	ABC	week2
8	2019	WK4	ABC	week2

Requirement:

Based on my formula I got the below mentioned output's On the fly:

"Formula for Scenario's:

Filters (In report):
Year,
level,
Customer,
WeekName,
Plan Y/N,

Distinct ID
1
2
3
4
5
6
7
8

Total Weeks Distinct Count : 8

Scenario-1:

DAX:
Scenario-1 = CALCULATE(DISTINCT('Table1'[id]),
ALLEXCEPT('Table1',Table1[Year],Table1[Level],Table1[Customer],Table1[WeekName]))

Output for After Filter Applied, Year:2019, Level:WK4, Customer:ABC and Week Name: Week1
o/p Count: 5
OutPut List:
1
2
3
4
5

Scenario-2:

DAX :
Scenario-2 = CALCULATE(DISTINCT('Table1'[id]),
ALLEXCEPT('Table1',Table1[Year],Table1[Level],Table1[Customer])"

Output for After Filter Applied, Year:2019, Level:WK4, Customer:ABC and Week Name: All
o/p Count: 8
OutPut List:
1
2
3
4
5
6
7
8

Output for PLan "Yes":
Plan "Yes" (Switch Table)
calculation is : Scenario-1/scenario-2
5/8 *100 = 62.50%

Lists id's: 1,2,3,4,5

Expected Output :

o/p Count: OutPut List:
3
Expected Lists id's: 6,7,8

Request you to kinldy help and revert. If, any clarification required.

Thank in advance,

Arjun.Pandi

[Greg_Deckler]

@smpowerbi I am not clear on what you are looking for here. Particularly, I do not understand "Plan". What is "Plan"?

[smpowerbi]

@Greg_Deckler 

Hi Sir,

Plan is separate table, which contains YES and NO values.

For example this is the actual table,

Here Total distinct count = 8 (Id : 1-8 )

Id	year	Level	Customer	weekname
1	2019	WK4	ABC	week1
2	2019	WK4	ABC	week1
3	2019	WK4	ABC	week1
4	2019	WK4	ABC	week1
5	2019	WK4	ABC	week1
6	2019	WK4	ABC	week2
1	2019	WK4	ABC	week2
2	2019	WK4	ABC	week2
3	2019	WK4	ABC	week2
4	2019	WK4	ABC	week2
7	2019	WK4	ABC	week2
8	2019	WK4	ABC	week2

If I filter week1, I would get  the below list,

Here distinct count = 5,

Id	year	Level	Customer	weekname
1	2019	WK4	ABC	week1
2	2019	WK4	ABC	week1
3	2019	WK4	ABC	week1
4	2019	WK4	ABC	week1
5	2019	WK4	ABC	week1

If we choose YES, Based on the above filter selection(week 1).  we should export the above id’s from above table.( filtered distinct count )

If we choose NO, we should export the remaining list of id’s from the below table.(total distinct count – filtered distinct count).  The id’s should not be same from the above filtered table.

Id	year	Level	Customer	weekname
6	2019	WK4	ABC	week2
7	2019	WK4	ABC	week2
8	2019	WK4	ABC	week2

Thanks and regards,

Arjun P

[Greg_Deckler]

OK, for the problem described, this will get you your 3 rows:

Measure = 
VAR __Table1 = SELECTCOLUMNS('Table1',"__Id",[Id])
VAR __Table2 = 
    ADDCOLUMNS(
        ALL(Table1),
        "__NotIn",[Id] IN __Table1
    )
RETURN
    COUNTROWS(FILTER(__Table2,NOT([__NotIn])))

I have attached a PBIX. Sorry, having a hard time trying to follow this one.

[smpowerbi]

@Greg_Deckler 

Hi Sir, 

Thank you for your reply.

How to get the detail of three?

WhenI I Export data,  i need only the id column, not the entire table.(id : 6,7,8)

Thanks in Advance.

Regards,

Arjun P

[Greg_Deckler]

@smpowerbi - You would have to create disconnected tables for your selection slicers and then implement something like Inverse Selector or Complex Selector. I have attached the PBIX files.

[smpowerbi]

Dear @Greg_Deckler 

Greetings !!! 

Thanks for your Suggestion. I have worked the same and getiing inverse number for unselected Slicers. but we have multiple slicers as well as the results should be store in a Column/Table to show the remaining data.

I Kindly request you to sort out on this.. also i shared the below Link for clear requirement.

https://filebin.net/kywxo2d7hrfjeuxx

Thanks a Lot!!

[smpowerbi]

Dear @Greg_Deckler 

please help me to solve this.

[smpowerbi]

@Greg_Deckler 

Hi sir,

Thanks for the reply.

Plan is a separate table, which contains YES and NO filter option.

For example this is the actual table,

Here Total distinct count = 8 (Id : 1- 8 )

Id	year	Level	Customer	weekname
1	2019	WK4	ABC	week1
2	2019	WK4	ABC	week1
3	2019	WK4	ABC	week1
4	2019	WK4	ABC	week1
5	2019	WK4	ABC	week1
6	2019	WK4	ABC	week2
1	2019	WK4	ABC	week2
2	2019	WK4	ABC	week2
3	2019	WK4	ABC	week2
4	2019	WK4	ABC	week2
7	2019	WK4	ABC	week2
8	2019	WK4	ABC	week2

If I filter week1, I would get  the below list,

Here distinct count = 5,

Id	year	Level	Customer	weekname
1	2019	WK4	ABC	week1
2	2019	WK4	ABC	week1
3	2019	WK4	ABC	week1
4	2019	WK4	ABC	week1
5	2019	WK4	ABC	week1

Now here is my actual work, After filtering i should export the actual filtered distinct datas and the remaining distinct datas.

If we choose YES, Based on the above filter selection(week 1).  we should export the id’s from above table.( filtered distinct count )

If we choose NO, we should export the remaining list of id’s .(total distinct count – filtered distinct count = 3).  The id’s should not be same from the above filtered table.

If i export the from NO option, i should get the id's list like below table.

Id	year	Level	Customer	weekname
6	2019	WK4	ABC	week2
7	2019	WK4	ABC	week2
8	2019	WK4	ABC	week2

.

Thanks and Regards,

Arjun P