Filter Multiple Scorecards using one date slicer

[Anonymous]

Hi everyone,

I'm creating an attendance tracker that counts how many people did 5 courses. Each course contains the completion date.

I used a slicer to filter by year (i input the 5 dates in the field and assigned as 'Date Hierarchy').

Filter 2021, Course 3 scorecard count is BLANK (but there are participants who  completed course 3 in 2021???)

Filter 2018, Course 1 scorecard counts correctly (counts the dates from 2018), Course 3 counts the 2021 date.

Crosschecking with the Excel sheet, It seems like that the Course 2-5 scorecards count the dates IF they completed Course 1.

How do I make the other scorecards rigid and not dependent on Course 1?

for example, when I filter 2021, it would only show the people who completed Course 1-5 in 2021.

Thank you!

[Anonymous]

Hi everyone,

I'm creating an attendance tracker that counts how many people did 4 courses. Each course contains the completion date.

I used a slicer to filter by year (i input the 4 dates in the field and assigned as 'Date Hierarchy').

Filter 2021, Course 3 scorecard count is BLANK (but there are participants who  completed course 3 in 2021???)

Filter 2018, Course 1 scorecard counts correctly (counts the dates from 2018), Course 3 counts the 2021 date.

Crosschecking with the Excel sheet, It seems like that the Course 2-5 scorecards count the dates IF they completed Course 1.

How do I make the other scorecards rigid and not dependent on Course 1?

for example,  when I filter 2021, it should only show the people who completed Course 1-4 in 2021.

Here's the data: https://docs.google.com/spreadsheets/d/1kCwPFTp-VdcSIMuU8hfx2W09ntP3rXgf3_kJjjUe-FI/edit?usp=sharing

Empty cells means they havent attended the courses.

PowerBI: https://drive.google.com/file/d/1FbHKEWEMLogKStB3H58E0nO6FAUJXYYM/view?usp=sharing

I did not do any Measure or Relationships. Just a simple right click of the field values and 'Count'.

Let me know how I can improve further!

[Hussain7676]

Hi,

Please go through the follow link based on relative slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[Fowmy]

@Anonymous 

If you can share sample data or a Power BI file with sample data that represent your model, it will greatly help find a solution for your problem.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platform and share the link here.

[Anonymous]

@Fowmy 

This is a sample Power Bi

If I filter 2021, C3 is blank (it's not suppose to be coz people completed C3 in 2021)

Sample Data:

As you can see, there are 2021 dates in C3 but did not reflect on scorecard. The count depends on the C1 date.

[Fowmy]

@Anonymous 

I cannot understand how you have created the measure and built relationships. Not enough information on the file to identify the problem. 
Save a copy of the file removing confidential data, save it in google drive or one drove then share the link here.

[Anonymous]

Hi @Fowmy 

Here's the data: https://docs.google.com/spreadsheets/d/1kCwPFTp-VdcSIMuU8hfx2W09ntP3rXgf3_kJjjUe-FI/edit?usp=sharing

Empty cells means they havent attended the courses.

PowerBI: https://drive.google.com/file/d/1FbHKEWEMLogKStB3H58E0nO6FAUJXYYM/view?usp=sharing

I did not do any Measure or Relationships. Just a simple right click of the field values and 'Count'.

Let me know how I can improve further!

[Icey]

Hi @Anonymous ,

Please check the attached .pbix file.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Icey ,

Could you please walk me through how you get  the results?

Like how you got the "Dates"? Did you combine the 4 Courses? How?

[Icey · Accepted solution]

Hi @Anonymous ,

Sure, let me explain clearly.

1. Go to Power Query Editor and UnPivot all columns.

And you will get this:

2. Close and Apply.

3. Create a calculated table and create relationship between the Dates table and the fact table.

Dates = CALENDARAUTO()

4. Then you can use the Date column from Dates table. And the Date hierarchy is generated automatically based on this option:

For more details, please refer to this document: Auto date/time in Power BI Desktop - Power BI | Microsoft Docs

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.