PowerBI slicers/filters on two different tables

Question

In PowerBI, is it possible two filters or slicers on two different tables such that I can filter either table or both tables and joining the data in the display?

 create table person(
    id int not null,
    name char(30) not null,
    grp char(1) not null)
 create table time(
    person_id int not null,
    state char(10) not null,
    date datetime not null,
    hours int not null)
go
  insert person values (1,'PersonA1','A')
  insert person values (2,'PersonA2','A')
  insert person values (3,'PersonA3','A')
  insert person values (4,'PersonB1','B')
  insert person values (5,'PersonB2','B')
  insert person values (6,'PersonB3','B')
  insert time values(1,'Work','2020-02-01',8)
  insert time values(2,'Work','2020-02-02',8)
  insert time values(1,'Vacation','2020-03-01',8)
  insert time values(1,'Sick','2020-03-02',8)
  insert time values(3,'Work','2020-03-04',8)
  insert time values(4,'Work','2020-02-01',8)
  insert time values(2,'Sick','2020-02-02',8)
  insert time values(1,'Vacation','2020-03-01',8)
  insert time values(2,'Sick','2020-03-02',8)
  insert time values(2,'Work','2020-03-04',8)
  insert time values(4,'Vacation','2020-02-01',8)
  insert time values(5,'Work','2020-02-02',8)
  insert time values(1,'Vacation','2020-03-01',8)
  insert time values(2,'Sick','2020-03-02',8)
  insert time values(3,'Work','2020-03-04',8)

Where in PowerBI, I want to show the following version of a table displayed. Here I am using Pivoted Column on the time table and using managed relationship to join to outputs into table format:

No filters

|   person_id |   id | name     | grp   |   Sick |   Vacation |   Work |
|-------------|------|----------|-------|--------|------------|--------|
|           0 |    1 | PersonA1 | A     |      0 |          0 |      0 |
|           1 |    2 | PersonA2 | A     |      8 |         24 |      8 |
|           2 |    3 | PersonA3 | A     |     24 |          0 |     16 |
|           3 |    4 | PersonB1 | B     |      0 |          0 |     16 |
|           4 |    5 | PersonB2 | B     |      0 |          8 |      8 |
|           5 |    6 | PersonB3 | B     |      0 |          0 |      8 |

Add timeslicer or filter for date less than 2020-03-01

|   person_id |   id | name     | grp   |   Sick |   Vacation |   Work |
|-------------|------|----------|-------|--------|------------|--------|
|           0 |    1 | PersonA1 | A     |      0 |          0 |      0 |
|           1 |    2 | PersonA2 | A     |      0 |          0 |      8 |
|           2 |    3 | PersonA3 | A     |      8 |          0 |      8 |
|           3 |    4 | PersonB1 | B     |      0 |          0 |      0 |
|           4 |    5 | PersonB2 | B     |      0 |          8 |      8 |
|           5 |    6 | PersonB3 | B     |      0 |          0 |      8 |

Add timeslicer or filter date for greater than 2020-03-01

|   person_id |   id | name     | grp   |   Sick |   Vacation |   Work |
|-------------|------|----------|-------|--------|------------|--------|
|           0 |    1 | PersonA1 | A     |      0 |          0 |      0 |
|           1 |    2 | PersonA2 | A     |      8 |         24 |      0 |
|           2 |    3 | PersonA3 | A     |     16 |          0 |      8 |
|           3 |    4 | PersonB1 | B     |      0 |          0 |     16 |
|           4 |    5 | PersonB2 | B     |      0 |          0 |      0 |
|           5 |    6 | PersonB3 | B     |      0 |          0 |      0 |

Along with a filter on grp to show combinations of A and B or just A or B.

Using two filters like, Grp = A and date < 2020-03-01 filter

|   person_id |   id | name     | grp   |   Sick |   Vacation |   Work |
|-------------|------|----------|-------|--------|------------|--------|
|           0 |    1 | PersonA1 | A     |      0 |          0 |      0 |
|           1 |    2 | PersonA2 | A     |      0 |          0 |      8 |
|           2 |    3 | PersonA3 | A     |      8 |          0 |      8 |

Or, Grp = B and date > 2020-03-1 filter

|   person_id |   id | name     | grp   |   Sick |   Vacation |   Work |
|-------------|------|----------|-------|--------|------------|--------|
|           3 |    4 | PersonB1 | B     |      0 |          0 |     16 |
|           4 |    5 | PersonB2 | B     |      0 |          0 |      0 |
|           5 |    6 | PersonB3 | B     |      0 |          0 |      0 |

Or any combination of the two filters?

Answer 1

It looks to be very basics of Power BI. Create a Relationship.

This makes everything work as you expect.

No filters

Using slicers by Date and Group

Display BLANK as zeros

*Added as per the comments from OP

In above examples, data with no values are missing in the matrix visualization (e.g. "PersonA3" and "Vacation" in the second figure).

This is because, default "Sum" summarization returns BLANK for no data, and the "Matrix" visual does not display anything for BLANK.

If you want to have "zero" instead of BLANK, you need to explicitly define a DAX Measure which returns zero for no data.

There are ways to do that, but here is probably the simplest way.

1. Create separate table for slicers.

Factor out the States dimension into a separate dimension table, then create a relationship with the original Times table.

This is necessary to make sure that the Matrix visual shows all States regardless of whether the State exists in the Times table affected by the filters/slicers.

2. Define a DAX measure.

Default Sum measure returns a BLANK if there is no data falling into the current filter context. You need to define a new measure that returns zero when there is no data. Here is probably the simplest formula for it.

Total Hours = SUM ( Times[Hour] ) + 0

The matrix is filled with zeros, and "PersonA3" and "Vacation" came back.

Answer 2

Yes, it is possible when you use powerquery

1. For filtering, you can use Table.SelectRows

https://docs.microsoft.com/en-us/powerquery-m/table-selectrows

It will more-less looks like (I didn't tested this)

filteredTable = Table.SelectRows(yourTable, each [date] <= #datetime(2020, 3, 01, 0, 0, 0)

it will give you new table as response

2. For returning modified table (with different columns) you can use Table.TransformRows

https://docs.microsoft.com/en-us/powerquery-m/table-transformrows

Table.TransformRows(
    yourFilteredTable,
    (row) as record => [person_id, id , name, grp, Sick, Vacation, Work  ]
)

it will return you new table

3. for merging tables you can use Table.Combine - you give two tables and you will get one merged

https://docs.microsoft.com/en-us/powerquery-m/table-combine

Table.Combine({
    tableOne,
    tableTwo
})