Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a data set as below:
Date | Data |
1/1/2019 | A |
1/1/2019 | B |
1/1/2019 | C |
1/1/2019 | D |
1/1/2019 | E |
1/1/2019 | F |
1/1/2019 | G |
1/1/2019 | H |
1/1/2019 | I |
1/1/2019 | J |
1/2/2019 | A |
1/2/2019 | B |
1/2/2019 | C |
1/2/2019 | D |
1/2/2019 | E |
1/2/2019 | F |
1/2/2019 | G |
1/2/2019 | H |
1/2/2019 | I |
1/2/2019 | J |
1/2/2019 | K |
1/2/2019 | L |
1/2/2019 | M |
1/2/2019 | N |
1/2/2019 | O |
1/3/2019 | A |
1/3/2019 | B |
1/3/2019 | C |
1/3/2019 | D |
1/3/2019 | E |
1/3/2019 | F |
1/3/2019 | G |
1/3/2019 | H |
1/3/2019 | I |
1/3/2019 | J |
1/3/2019 | K |
1/3/2019 | L |
1/3/2019 | M |
1/3/2019 | N |
1/3/2019 | O |
1/3/2019 | P |
1/3/2019 | Q |
1/3/2019 | R |
1/3/2019 | S |
1/3/2019 | T |
1/3/2019 | U |
1/3/2019 | V |
1/3/2019 | W |
1/3/2019 | X |
1/3/2019 | Y |
1/3/2019 | Z |
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!!
Solved! Go to 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 () )
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |