cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Distinct count with date parameter

Hello everyone! Hope you all are having a nice day.

I'm looking for some help with something that is stressing me a lot in the past few days.

So, what I'm trying to do is to get the amount of lives each person had to watch since their registration date, and then compare it to how many they really watched. It's for a engagement study I'm doing.

The table should look like this:

 Name Registration Date [A] lives streamed since registration date [A] lives watched [B] lives streamed since registration date [B] lives watched [C] lives streamed since registration date [C] lives watched Peter 23/08/2021 3 2 2 0 3 1 Jordan 07/11/2016 3 1 2 0 3 1 Alisha 25/10/2021 3 0 2 1 1 1 Mike 21/06/2013 3 0 2 1 3 0

This is the table that I'm getting the info from:

 Live Name Date Live Name Live [A] - 04/12/2021 04/12/2021 Live [A] Peter Live [A] - 06/12/2021 06/12/2021 Live [A] Peter Live [A] - 12/12/2021 12/12/2021 Live [A] Jordan Live [B] - 16/03/2022 16/03/2022 Live [B] Alisha Live [B] - 24/04/2022 24/04/2022 Live [B] Mike Live [C] - 01/10/2021 01/10/2021 Live [C] Alisha Live [C] - 04/11/2021 04/11/2021 Live [C] Peter Live [C] - 01/09/2021 01/09/2021 Live [C] Jordan

I'm using a one to many, crossfiltered relation between these tables.

If anyone knows how to get this to work and could spare some time to explain it to me, I'd really appreciate it!

1 ACCEPTED SOLUTION
Super User

You will need to add another dimension table to deal with the blanks. Here is a sample implementation.

4 REPLIES 4
Super User

Which table has the Registration Date information?  How does your data model look like?

Regular Visitor

Hello @lbendlin
Sorry, I forgot about the table with the registration date. It's this one:

 Name Registration Date Category Peter 23/08/2021 Diamond Jordan 07/11/2016 Gold Alisha 25/10/2021 Diamond Mike 21/06/2013 Silver

My data model is this:

An one to many relation with "Reg" crossfiltering the "Table".

Super User

You will need to add another dimension table to deal with the blanks. Here is a sample implementation.

Regular Visitor

Thanks a lot @lbendlin !

It really helped me a lot.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors