Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION

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

1.PNG

 


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.

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.

View solution in original post

4 REPLIES 4
v-lid-msft
Community Support
Community Support

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

 

17.PNG18.PNG

 


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.

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

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.

 

1.PNG

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.

 

2.PNG

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.

 

3.PNG

 

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.

 

4.PNG

 

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 

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

1.PNG

 


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.

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

Thanks a lot @v-lid-msft . It helps!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.