Research grants, hiring and departure date

[Rattesp]

Hello, I would like some help. I'm new to PowerBi and have a problem that I can't seem to solve. My fact table contains information on our researchers' research funding over the last five years. Each line represents a grant received by a researcher (grant amount, type, sub-type, title of research project, etc.). Obviously, if a researcher has not received a grant, he or she does not appear in this table. In a DimTable, I have information about all the researchers, including their hire date and departure date, if applicable. I want to create a matrix showing each researcher's name and annual research funding for each of the last five years. However, I need to take into account the researchers' hiring and departure dates in this matrix. If a professor was employed during these five years but did not receive funding, I want to return $0 for each of the years in which he did not receive funding. However, if he wasn't active in the first two years because he hadn't yet been hired, I don't want to return a $0, I want to return a blank to indicate that he wasn’t active that year.

Name	2019	2020	2021	2022	2023
AAA	$25 000	$18 000	$0	$15 000	$0
BBB			$5 000	$75 000	$0
CCC	$5 000	$9 000	$15 000	$6 000	$18 000
DDD	$15 000	$0

AAA was active during all 5 years

BBB was hired in 2021
CCC was active during all 5 years

DDD retired in 2021

Thank you for your help !

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how the semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: =
VAR _condition =
    COUNTROWS (
        FILTER (
            researcher,
            MAX ( 'year'[YEAR] ) >= YEAR ( researcher[Hiring Date] )
                && OR (
                    MIN ( 'year'[YEAR] ) <= YEAR ( researcher[Departure Date] ),
                    researcher[Departure Date] = BLANK ()
                )
        )
    ) = 1
RETURN
    IF ( _condition, COALESCE ( SUM ( funding[GrantAmount] ), 0 ) )

[v-yifanw-msft]

Hi @Rattesp 

What did your original data look like? What kind of results do you hope to get in the end? Obviously AAA was funded at 0 in 2021 as well as 2023, but why was he active for all 5 years? DDD was funded at 0 in 2020, so why was he retired in 2021?

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Yifan

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

[Rattesp]

Hi,

Thank you for your reply. Here is a simplified version of my data :

Funding Table

Researcher Name	Grant Amount	Grant Type	Funding Agency	YEAR
AAA	25 000,00  $	AZT	NIH	2019
CCC	5 000,00  $	PTT	NSERC	2019
DDD	10 000,00  $	PTT	NIH	2019
DDD	5 000,00  $	DDQ	FQTR	2019
AAA	18 000,00  $	AZT	NIH	2020
CCC	9 000,00  $	PTT	FQTR	2020
BBB	5 000,00  $	PTT	NIH	2021
CCC	5 000,00  $	PTT	FQTR	2021
CCC	10 000,00  $	DDQ	FQTR	2021
AAA	15 000,00  $	AZT	NIH	2022
BBB	75 000,00  $	PTT	NIH	2022
CCC	6 000,00  $	PTT	FQTR	2022
CCC	18 000,00  $	PTT	NSERC	2023

Researchers Table

Researcher Name	Status	Hiring Date	Departure Date
AAA	Researcher	2012-05-22
BBB	Junior researcher	2021-06-06
CCC	Researcher	2015-01-18
DDD	Senior researcher	1989-02-24	2021-02-17

And here is the result I would like to get :

Researcher Name	2019	2020	2021	2022	2023
AAA	$25 000	$18 000	$0	$15 000	$0
BBB			$5 000	$75 000	$0
CCC	$5 000	$9 000	$15 000	$6 000	$18 000
DDD	$15 000	$0

As you can see BBB doesnt appear in the Funding table before 2021 because he hadn't been hired yet. That's why I want to show a blank for him in 2019 and 2020 in my matrix. He simply couldn't get a research grant because he hadn't yet been hired. In the case of AAA, he was already working at our research institute since 2012 but he did not get a research grant in 2021 and 2023, so I want to see a $0 for him in 2021 and 2023. He was "active" during those years but he did not get any grants (maybe his grant application was turned down by the granting agency) 

I should have specified that researchers must apply for grants from external funding agencies. Their applications are not always accepted, in which case they are not funded for a year. It can also happen that a researcher does not apply for a grant in a given year because he or she still has a lot of funds available from a previous grant. In my example, I've used small amounts, but in reality, funding is often in the hundreds of thousands of dollars.

In the case of CCC, he received funding every year. As for DDD, he received funding in 2019 but not in 2020, hence the $0 I would like to see (maybe he didn't apply for a grant for 2020 because he intended to retire the following year). From 2021 to 2023, I want to see a blank because he retired in 2021 after 32 years at our research institute, which means he was not "active" in 2021, 2022 and 2023 so he couldn't have applied for a grant).   

I hope I've made myself clearer.

Thank you again for your help.

[v-yifanw-msft · Accepted solution]

Hi @Rattesp ,

Please follow these steps:

1.        add new table(DO NOT create any relationship with other fact table)

Years =
VALUES ( 'Funding Table'[YEAR] )

2.        add new measure in Funding Table

Measure = 
VAR _researcher =
    SELECTEDVALUE ( 'Funding Table'[Researcher Name] )
VAR _year =
    SELECTEDVALUE ( 'Years'[Year] )
VAR _depature =
    CALCULATE (
        MAX ( 'Researchers Table'[Departure Date] ),
        FILTER (
            ALLSELECTED ( 'Researchers Table' ),
            'Researchers Table'[Researcher Name] = _researcher
        )
    )
VAR _gamt =
    CALCULATE (
        SUM ( 'Funding Table'[Grant Amount] ),
        FILTER ( 'Funding Table', 'Funding Table'[YEAR] = _year )
    )
VAR _min1year =
    CALCULATE (
        MIN ( 'Funding Table'[YEAR] ),
        FILTER (
            ALLSELECTED ( 'Funding Table' ),
            'Funding Table'[Researcher Name] = _researcher
                && SUM ( 'Funding Table'[Grant Amount] ) > 0
        )
    )
VAR _maxyear =
    CALCULATE ( MAX ( 'Funding Table'[YEAR] ), ALLSELECTED ( 'Funding Table' ) )
RETURN  
    IF (
        ISBLANK ( _gamt )
            && _year > _min1year
            && _year
                < IF ( ISBLANK ( _depature ), _maxyear + 1, YEAR ( _depature ) ),
        0,
        _gamt
    )

3.         create a Matrix

4.final output

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Yifan Wang}

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

[FreemanZ]

hi @Jihwan_Kim ,

could you spare some time to check this post?

The requirement and expectation are both clear. I thought it was easy but got stuck on the matrix cells with no record, like AAA in 2023. 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how the semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

expected result measure: =
VAR _condition =
    COUNTROWS (
        FILTER (
            researcher,
            MAX ( 'year'[YEAR] ) >= YEAR ( researcher[Hiring Date] )
                && OR (
                    MIN ( 'year'[YEAR] ) <= YEAR ( researcher[Departure Date] ),
                    researcher[Departure Date] = BLANK ()
                )
        )
    ) = 1
RETURN
    IF ( _condition, COALESCE ( SUM ( funding[GrantAmount] ), 0 ) )

[Rattesp]

This is brillant ! Thank you very much for your help !