Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Filter Multiple Scorecards using one date slicer

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!

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

 

Sure, let me explain clearly.

 

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

unpivot.png

 

And you will get this:

unpivot 2.PNG

 

2. Close and Apply.

 

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

Dates = CALENDARAUTO()

relationship.PNG

 

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

time.PNG

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.

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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!

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
Super User
Super User

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

@Fowmy 

 

This is a sample Power Bi

EM_17_20_4-1621760575459.png

 

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

EM_17_20_5-1621760589337.png

 

Sample Data:

EM_17_20_0-1621761069786.png

 

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

 

@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.

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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
Community Support
Community Support

Hi @Anonymous ,

 

Please check the attached .pbix file.

count.PNG

 

 

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
Not applicable

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?

EM_17_20_0-1621940725128.png

 

Icey
Community Support
Community Support

Hi @Anonymous ,

 

Sure, let me explain clearly.

 

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

unpivot.png

 

And you will get this:

unpivot 2.PNG

 

2. Close and Apply.

 

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

Dates = CALENDARAUTO()

relationship.PNG

 

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

time.PNG

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.