Nested if statements in matrix

[Richard77]

Dear community,

I am hopin you will be able to help me on this matrix visualisation I am trying to create, but I am stuck.

I have 3 tables:

A standard date table

A table with service data (start date, organisation ID), simplified example data:

service	start_date	organisation_id
10728	08/10/2018	5
10820	17/11/2018	2
10820	17/11/2018	5
10940	20/01/2019	2199
10940	20/01/2019	2200
10940	20/01/2019	2201
10940	20/01/2019	2202
10940	20/01/2019	2203
11098	17/03/2019	3583

A table with staff data (Organisation ID, number of staff, year a baseline year (static per organisation) and a date being always 01-01-[year]), simplified example data (ID 3085 thus has no baseline data):

organisation_idstaffyearDateBaseline year

1158	7.0	2017	01/01/2017	2019
1158	5.0	2018	01/06/2018	2019
1158	8.0	2019	01/01/2019	2019
1158	9.0	2020	01/01/2020	2019
1158	9.0	2021	01/01/2021	2019
1158	12.8	2022	01/01/2022	2019
1158	0.0	2023	01/01/2023	2019
3085	7.2	2019	01/01/2019	2018
3085	9.0	2020	01/01/2020	2018
3085	10.8	2021	01/01/2021	2018
3085	11.4	2022	01/01/2022	2018
3085	0.0	2023	01/01/2023	2018

Now I want to create a matrix. In the rows I want 'Organisation ID', in the columns 'Year' and as values one of the following options:

- "n/a" for all columns if the applicable organisation has no baseline data (i.e. there is no row in 'staff data' where year equals baseline year.

- number of staff per organisation, per year

- "n/s" when the Organisation ID is not in the 'service' table, hence did not receive services in that year.

So far I have only managed to create a matrix that shows a combination of 2 of the expected values, but not 3.  This being my closest measure: 

SumStaffFilter =

IF (

    ISBLANK (

        CALCULATE (

            SUM ( 'staff'[number of staff] ),

            FILTER (

                'staff',

                'staff'[organisation_id]

                    IN VALUES ( 'service'[organisation_id] )

            )

        )

    ),

    "n/s",

    CALCULATE (

        SUM ( 'staff'[number of staff] ),

        FILTER (

            'staff',

            'staff'[organisation_id]

                IN VALUES ( 'service'[organisation_id] )

        )

    )

)

Any attempt to incorporate a measure that gives "n/a" for the whole row overwrites my 'number of staff' thus showing "n/a" instead of a number.

I woul really appreciate your help on this one. If there is any other information required I'm happy to oblige.

Cheers,

[Richard77]

Dear @v-heq-msft 

Thank you again for your efforts. Hereby a link to my OneDrive with a .pbix file where I included the (cleaned up) data + a mock up of the result.

Example data.pbix

Cheers,

[v-heq-msft]

Hi @Richard77 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

Create two measures

HasBaseline = 
IF(
    COUNTROWS(
        FILTER(
            'staff',
            'staff'[year] = 'staff'[Baseline year]
            && 'staff'[organisation_id] = MAX('staff'[organisation_id])
        )
    ) > 0,
    BLANK(),
    "n/a"
)

Sum_StaffFilter = 
VAR CurrentOrgID = MAX('staff'[organisation_id])
VAR OrgHasService = NOT(ISBLANK(SUM('service'[organisation_id])))
VAR OrgHasBaseline = [HasBaseline] = BLANK()
RETURN
IF(
    NOT(OrgHasBaseline),
    "n/a",
    IF(
        OrgHasService,
        CALCULATE(
            SUM('staff'[staff]),
            'staff'[organisation_id] = CurrentOrgID
        ),
        "n/s"
    )
)

Final output

Best regards,

Albert He

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

[Richard77]

Dear @v-heq-msft ,

thank you so much for your help! The measures do not completely work, as a result I would like to create a matrix like below where the yearly values for ID = 308 would be "n/a" as this organisation does not have a baseline (I've marked the baseline year orange in the screenshot below).

Using your measures this is the outcome (where it looks like the "n/a" overwrites the number of staff for each year (except for the baseline) and the "n/s".

Cheers,

[v-heq-msft]

Hi @Richard77 ,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.

Best regards,

Albert He