Forum Discussion
Nested if statements in matrix
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
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,
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