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
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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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