Dynamic "Table" based on slicer

[mcomsto]

Hi,

I have the following scenario, but I would like to generate the results based on a slicer for the date range.  Is this possible?  Or is there another way to achieve the results?

Scenario:

Patient Table

Patient Activity Table

Filter Patient Activity based on the date range, but show all patients regardless if they have any activities during the date range selected.

I would like to have a table visualization in PowerBI with a date range slicer that will allow the user to change the date range, but still return all patients with or without activity during the selected period.

This DAX works, but its not dynamic.

PatientsAndActivity = GENERATEALL('Patients',CALCULATETABLE('Patient Activity','Patient Activity'[Activity Completed Date Key] > 20180420, 'Patient Activity'[Activity Completed Date Key] < 20180428))

Blank values in the Activity and Activity Completed Date Key columns mean there is no activity for the patient during the designated date range.

PHP.Person ID	Activity	Activity Completed Date Key
1	Email	20180425
1	InPersonMeeting	20180426
1	PhoneCall	20180427
1	Email	20180427
1	PhoneCall	20180427
2	Email	20180425
2	PhoneCall	20180427
2	Email	20180427
3
4	PhoneCall	20180424
5
6	PhoneCall	20180427
7

If anyone can help, it will be greatly appreciated.

[samdthompson]

So you are wanting all patients to appear in the table even if there is no activity? In that case I would create a patient table with a single directional filter to the fact table, in a simplaitic form it would just be a new table =SUMMARIZE(FACT[PATIENT_NUMBER]). You would then build the table visualisation with the patient number from the Patient table, the activity from the fact table. Just make sure that the relationship to the suppleir table is a single direction.

// If this is a solution please mark as such

[mcomsto]

@samdthompson, thank you for the reply.  I just need a little clarification.

You mentioned a supplier table, I assume you are referring to the "new table" i need to create, correct?

The relationship you mentioned creating in a single direction, is that a relationship between the "new table" and the patient activity/fact table?

matt

[mcomsto]

@samdthompson,

I have tried your suggestion and it is not working.  When I apply the activity fact date slicer not all patient are returned in the table visualization, only patients with activity during the time period.  Any additional ideas?

My tables and relationships are:

Patient Dim

Patient Activity Fact (M:1 Single with SummarizedPatientFact) and (M:1 Both with Patient Dim)

SummarizePatientFact with Patient ID SummarizePatientFact = SUMMARIZE('Patient Activity Fact','Patient Activity Fact'[Patient ID])

Thank you.

[samdthompson · Accepted solution]

Ah, you have just two tables yes? Make a date table. You can do this either by SQL and import like your other tables or make one on the fly:

Calendar = ADDCOLUMNS (FIRSTDATE(ACTIVITY_COMPLETED_DATE), LASTDATE(ACTIVITY_COMPLETED_DATE))

1. Mark this as a date table and create a relationship to your fact table. Use this as the basis for all your date stuff. It contains every date from the earliest one to the last one in your fact table, even dates for which you have no info.

2.  When you build the table visual on a blank page:

3. Drag patient names to the table. Check that all patients will be on the table. if not there is a problem with the table formula but from what you posted that sounds unlikely.

4. Create a date filter. Change the date and check if the patient names chnage. If they do its a problem with the relationship. Again from what you describe the relationship has been name correctly ie single direction from the patient table to the fact table.

5. Drag the activity column into the table visualisation.

Good luck.

Cheers

Sam

// If this is a solution please mark as such

[Ashish_Mathur]

Hi,

Based on that data shared by you, what result are you expecting?

[mcomsto]

I want the outcome to be what is in the table I added to the post.