Forum Discussion

mail2vjj's avatar
mail2vjj
Helper III
8 years ago

Opposite Intersection

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-1...
Need Help
Show and Tell
Tips and Tricks
mail2vjj's avatar
mail2vjj
Helper III
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    mail2vjj
    Helper III
    8 years ago

    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-18D
     E
     F
    06-01-18C
     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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        mail2vjj
        Helper III
        8 years ago

        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