Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Show the missing/added column

Hi,

 

I have a data set as below:

 

DateData
1/1/2019A
1/1/2019B
1/1/2019C
1/1/2019D
1/1/2019E
1/1/2019F
1/1/2019G
1/1/2019H
1/1/2019I
1/1/2019J
1/2/2019A
1/2/2019B
1/2/2019C
1/2/2019D
1/2/2019E
1/2/2019F
1/2/2019G
1/2/2019H
1/2/2019I
1/2/2019J
1/2/2019K
1/2/2019L
1/2/2019M
1/2/2019N
1/2/2019O
1/3/2019A
1/3/2019B
1/3/2019C
1/3/2019D
1/3/2019E
1/3/2019F
1/3/2019G
1/3/2019H
1/3/2019I
1/3/2019J
1/3/2019K
1/3/2019L
1/3/2019M
1/3/2019N
1/3/2019O
1/3/2019P
1/3/2019Q
1/3/2019R
1/3/2019S
1/3/2019T
1/3/2019U
1/3/2019V
1/3/2019W
1/3/2019X
1/3/2019Y
1/3/2019Z

 

Date 1is having values from A to J... Date 2 is having A to O and Date 3 is having A to Z. Now I want to compare the values of Date 1 and Date 2 and return the values which are not present.

 

Example when I select Date 2, it should show the values A to O and also it should show the value K to O as this is not present in Date 1. Respectively it should compare with all previous dates and return values. Any headsup!!!! Kindly help!!

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    Sorry for that I misunderstood the previous as all the days before selected day, we can use the following measures to fix it:

     

    Added = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)) )
RETURN
    IF (
        AND (
            NOT CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Added",
        BLANK ()
    )
    Missing = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)))
RETURN
    IF (
        AND (
            CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            NOT CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Miss",
        BLANK ()
    )

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    We can create a calculated table, which contain all the possible data value, then use two measures and table visual to archive your requirement.

     

    All = DISTINCT('Table'[Data])
    Added = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] < selectedDay )
RETURN
    IF (
        AND (
            NOT CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Added",
        BLANK ()
    )

     

    Missing =
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] < selectedDay )
RETURN
    IF (
        AND (
            CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            NOT CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Miss",
        BLANK ()
    )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello,

       

      Thanks for your response. This helps but with few data issues. Posting that here and looking forward from you.

       

      I have the below data: For Date 1, I am having data from A to J: And it is showing in added when i select date 1. Its fine as this is the First date.

       

      For Date 2, i am having data C to O, so when i select Date 2, it  is showing added data as K to O and missing data as A,B.

       

      Basically it compares with the previous day data and returns the values. Its also fine here.

       

      For Date 3, i am having data from A to W, so when i select Date 3, it is showing added data as P to W, but it also should show A,B as these two data is not available on Date 2, here this Fails.

       

       

      For Date 4, I am having data from A to E, here it compares with the previous day data and returns the missing data as F to W.

       

       

      It is working for Few dates and doesn't for Few. Please help me to fix this. Thanks in advance and sorry for the delay response.

       

      v-lid-msft 

      • v-lid-msft's avatar
        v-lid-msft
        Community Support
        6 years ago

        Hi Anonymous ,

         

        Sorry for that I misunderstood the previous as all the days before selected day, we can use the following measures to fix it:

         

        Added = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)) )
RETURN
    IF (
        AND (
            NOT CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Added",
        BLANK ()
    )
        Missing = 
VAR selectedDay =
    CALCULATE ( MAX ( 'Table'[Date] ) )
VAR t =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Date]<selectedDay)))
RETURN
    IF (
        AND (
            CONTAINS ( t, [Data], SELECTEDVALUE ( 'All'[Data] ) ),
            NOT CONTAINS ( 'Table', 'Table'[Data], SELECTEDVALUE ( 'All'[Data] ) )
        ),
        "Miss",
        BLANK ()
    )

         


        BTW, pbix as attached.

         

        Best regards,

        Community Support Team _ Dong Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.