Forum Discussion
Select column values which is missing on another table
Team, I have two Table like below
Table 1
| value1 | 2020/01 |
| value2 | 2020/02 |
| value3 | 2020/03 |
| value4 | 2020/04 |
| value5 | 2020/05 |
| value6 | 2020/06 |
Table 2
| value1 |
| value2 |
| value3 |
| value4 |
| value5 |
| value6 |
When I display values in chart, we will filter 1st Table with date coumn and that works fine. What I want is, I want to show another chart with missing data in the Table 1. which is like if I select date filter as 2020/01, I want to see value2, value3, value4, value5 and value6
Could someone help?
8 Replies
Anonymous
How is yor model set up? Are both tables related?
PaulDBrown I havent enabled the relationship now. If required, I can do that.
Anonymous
Here is one way. First, the model:
Now create a measure to identify the rows which are excluded from the selection in table 1:
Non Selected Table 2 Values = VAR SelT1 = VALUES ( Table1[Table1 Values] ) VAR T2Values = CALCULATETABLE ( VALUES ( Table2[Table2 Values] ), ALL ( Table1[Table1 Values] ) ) RETURN IF ( ISFILTERED ( Table1[Table1 Period] ), COUNTROWS ( EXCEPT ( T2Values, SelT1 ) ), 1 )Create the report page, select Table 2 and add this measure to the "Filters on this visual" in the filter pane and set the value to 1.
And you get this:
PaulDBrown I am fine with any solution, may be creating a new table/column with missing data and displaying or by using measures on existing column.
awesome PaulDBrown . That worked fine. I selected wrong tables and caused the issue. Thank you so much for your help.