Forum Discussion

Richard77's avatar
Richard77
Resolver I
2 years ago

Nested if statements in matrix

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:

servicestart_dateorganisation_id
1072808/10/20185
1082017/11/20182
1082017/11/20185
1094020/01/20192199
1094020/01/20192200
1094020/01/20192201
1094020/01/20192202
1094020/01/20192203
1109817/03/20193583

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

11587.0201701/01/20172019
11585.0201801/06/20182019
11588.0201901/01/20192019
11589.0202001/01/20202019
11589.0202101/01/20212019
115812.8202201/01/20222019
11580.0202301/01/20232019
30857.2201901/01/20192018
30859.0202001/01/20202018
308510.8202101/01/20212018
308511.4202201/01/20222018
30850.0202301/01/20232018

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,
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Richard77
      Resolver I
      2 years ago

      Dear Anonymous ,

       

      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,

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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

         

  • Richard77's avatar
    Richard77
    Resolver I
    2 years ago

    Dear Anonymous 

    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,