Opposite Intersection

[mail2vjj]

I have a table which looks like the following:

Date	Name	Holding	Change	Category
01-01-18	A	10	10	Founding
01-01-18	B	10	10	Founding
01-01-18	C	10	10	Public
02-01-18	A	20	10	Founding
02-01-18	B	30	20	Founding
02-01-18	C	40	30	Public
03-01-18	A	10	-10	Founding
03-01-18	B	50	20	Founding
03-01-18	C	30	-10	Public
04-01-18	a	100	90	Founding
04-01-18	b	100	50	Founding
04-01-18	c	100	70	Public
05-01-18	a	200	100	Founding
05-01-18	B	50	-50	Founding
05-01-18	D	125	1000	Public
06-01-18	a	100	-100	Founding
06-01-18	b	200	150	Founding
06-01-18	c	100	100	Public
06-01-18	d	75	-50	Public

I want to find 2 things which are the Opposite Intersection of the names which are not present in the previous date and present in the current date and vice versa.

Result 1:

For example:

Name D is present in on 5th Jan 2018 but was not present on 4th Jan 2018.

Name C is present on 6th Jan 2018 but was not present on 5th Jan 2018.

So the table will look like this, with the list of all the names for every particular date.

	Names
Date
05-01-18	D
06-01-18	C

Result 2:

For example:

Name C is present on 4th Jan 2018 but not present on 5th Jan 2018.

	Names
Date
05-01-18	C

So the table will look like this, with the list of all the names for every particular date.

Any help will be appreciated.

Thank you,

Vishesh Jain

[mail2vjj]

I have reached a partial solution, but it is only working if I choose a date on the slicer.

For result one this is the forumla I have used.

Result 1 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date])))
)

For Result 2 I just filpped the 'less than' and 'equal to' signs to change the tables.

Result 2 = EXCEPT(
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] < SELECTEDVALUE(Sheet1[Date]))),
CALCULATETABLE(VALUES(Sheet1[Name]), FILTER(ALLEXCEPT(Sheet1, Sheet1[Name]), Sheet1[Date] = SELECTEDVALUE(Sheet1[Date])))
)

However the problem I am facing here is that, since Name C has occured previously, it is not giving me the desired result, when is reoccurs on 6th Jan 2018.

Also, probably since I am using SELECTEDVALUES, the Matrix visual gives me an error until I select a date in the the slicer.

If anyone can correct my mistake or bulid on the formula that I am using, it would be of great help.

Thank you,

Vishesh Jain.

[Anonymous]

Hi @mail2vjj,

You can use below measure to get name list which not exist in last date.

Not exist In Previous = 
VAR _currDate =
    SELECTEDVALUE ( Table1[Date] )
VAR _prevDate =
    MAXX ( FILTER ( ALLSELECTED ( Table1 ), [Date] < _currDate ), [Date] )
VAR _current =
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = _currDate )
    )
VAR _previous =
    CALCULATETABLE (
        VALUES ( Table1[Name] ),
        FILTER ( ALLSELECTED ( Table1 ), Table1[Date] = _prevDate )
    )
RETURN
    IF (
        _prevDate <> BLANK (),
        CONCATENATEX ( EXCEPT ( _current, _previous ), [Name], "," )
    )

Regards,

Xiaoxin Sheng

[mail2vjj]

@AnonymousThank you for your response and sorry for not being clear about my question.

Your solution almost works for me, however I need the result as a list rather than seperated by commas.

So is there anything else that we can try to get the desired result.

	Name
Date
05-01-18	D
	E
	F
06-01-18	C
	A
	B

This is the sort of result I am looking for as my table will be consisting of thousands of names, so getting a list seperated by commas, will not be an optimal solution for me. 

I am trying to use the Matrix Visual as it will combine the date for me and give me the names in the row for every date.

This is just a dummy example of the result, actual result will consist of names in every row.

Again, thank you for all your help and sorry for not being clear enough in the first place.

Vishesh Jain

[Anonymous]

Hi @mail2vjj,

>>Your solution almost works for me, however I need the result as a list rather than seperated by commas.

Current dax seems not support to generate this table, I'd like to suggest you to use power query to achieve your requirement.

Sample:

Full query:

Spoiler

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZBPD4IgHIa/C2fZ+CNUx9K1Tq2784DazM10a3no2wca6E/RtSE6eHgfX5IExep9RwG6qqd5Xdq6qJpSf0UP1ZRmKdJA2b4+KA0SRCjWg+71+lE/lNjp3HZNf3JGnf6iIkDduqyu8oFhwMdWkhjwcWLRNcr4QmLRqY8v+2GPkAOhWBFyIORj2NQYOkj1RrN/WGaNWOYwsYXlDtvNlQIoGSEOhWFi2RJ7nCMWmyAmhri5VXqKYp9Wgqq/3/N4pacr3dAW5i6EK2Gh9As=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}, {"(blank).4", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}, {"Name", type text}, {"Holding", Int64.Type}, {"Change", Int64.Type}, {"Category", type text}}),
    #"Uppercased Text" = Table.TransformColumns(#"Changed Type1",{{"Name", Text.Upper, type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Uppercased Text",{"Date", "Name"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Date"}, {{"Name", each _[Name], type list}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Excepted", each Function.Invoke((current as list, previous as list) => 
    List.Difference(current,previous),{#"Added Index"{[Index]}[Name],#"Added Index"{[Index]-1}[Name]})),
    #"Removed Operation" = Table.RemoveRowsWithErrors(Table.RemoveColumns(#"Added Custom",{"Name", "Index"}), {"Excepted"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Operation", "Excepted")
in
    #"Expanded Custom"

Rgards,

Xiaoxin Sheng

[mail2vjj]

Hi @Anonymous

I tried to use your code to create a new query with my file but it gave me an error.

Here is the screenshot of the error. Maybe it might help you understand where am I going wrong here.

I am not really familiar to the advanced editor in the query.

So if you could please modify your code according to my file, that way I should be able to get a better understanding of your code and how it works.

Here is the link to the file in my one drive.

https://1drv.ms/x/s!Ap0qSKP-4qpThBkZj5kXIu1RAoSJ

And my apologies for the late response.

Thank you,

Vishesh Jain

[Anonymous]

HI @mail2vjj,

Maybe you can take a look at below sample, I modify the formula to use sheet result as reference.(you need to go to query editor and relocate your excel file)

Regards,

Xiaoxin Sheng

[mail2vjj]

Hi @Anonymous

 Your solution works!

I had to make a couple of tweaks to it, like change the name of file in the code and add the name of the columns.

The biggest change I had to make, though I didn't really understand why, was to move the 'Date' and 'Name' columns to the beginning of the table.

After that your solution worked but only for a table that already exists. I tried to use it with an Appended Table and it gave me an error.

However, after everything it is working now.

If you could please post your solution with the zip file again and mention the tweaks that I made, then it would help other people in the future.

Thank you so much for your help.

Vishesh Jain

[mail2vjj]

@Anonymous

Thank you for replying.

I was going over your solution and saw some steps like 'Grouped Rows' and you added a custom column as well.

I wanted to know if these steps are readily available in Power BI or did you have to write a custom code for it.

I am trying to fix your solution into my actual file but for some reason it is not working, even after I changed the 'Source' in the first line of your code to my existing table.

I'll let you know as soon as I a figure that out.

Thank you again for all your help.

Vishesh Jain

[Zubair_Muhammad]

@mail2vjj

Try this. From Modelling Tab>>> NEW TABLE

New Table =
FILTER (
    SUMMARIZE (
        TableName,
        TableName[Date],
        TableName[Name],
        "Missing", PREVIOUSDAY ( TableName[Date] )
            = CALCULATE (
                MAX ( TableName[Date] ),
                TableName[Date] < EARLIER ( TableName[Date] )
            )
    ),
    TableName[Date] <> DATE ( 2018, 1, 1 )
        && [Missing] = FALSE
)