Date filter on related tables

[Anonymous]

Hi 🙂

I have two tables (Employees and Assessment) that are related by column ID.
In the Employees table, I have the following calculated column:

QTD. ASSESSMENT = CALCULATE(COUNTROWS(Assessment),FILTER(Assessment,Assessment[ID]=EARLIER(Employees[ID])

))

This calculated column counts how many times each ID in the Employee table is repeated in the Assessment table. I wanted this count of my calculated column to be conditioned to the filter I made on the Assessment table.
For example, if in my dashboard I filter only assessments from the year 2019, my calculated column in the Employee table would only do the countrows in the Assessments table using rows with date=2019. Something like:

QTD. ASSESSMENT = CALCULATE(COUNTROWS(Assessment),FILTER(Assessment,Assessment[ID]=EARLIER(Employees[ID])
                                   && Assessment[Date].Year = 2019

))

But I want this to be in the dashboard filter, so the user can filter the year he wants to see. How can I do that?

[Anonymous]

Hi @Anonymous ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Stephen Tao

[Anonymous]

Hi @Anonymous ,

Try SELECTEDVALUE function.

QTD. ASSESSMENT = CALCULATE(COUNTROWS(Assessment),FILTER(Assessment,Assessment[ID]=EARLIER(Employees[ID])
                                   && Assessment[Date].Year = SELECTEDVALUE('TableName'[Year])

))

Best Regards,

Stephen Tao

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

[TomMartens]

Hey @Anonymous ,

if you want to the calculation reflect the interaction of your users then you can't use a calculated column, instead, you have to create a measure. Calculated Columns will only be evaluated during data refresh and not on interaction with the data model, e.g. by slicers.

I have to admit that I do not fully understand how these tables are related, as your script creates the "relationship" inside the FILTER, this is not necessary if there is a relationship between both tables: on the one side the employees table and on the many side the assessment table. This article provides a short introduction on how to create relationships in Power BI Desktop: Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

Maybe you can create a pbix that contains sample data but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

Hopefully, this provides some ideas on how to tackle your challenge.

Regards,

Tom