Power BI Report Builder Indicator Formula

Question

I am adding in an indicator to a PBI Report Builder Report. The indicator is based off multiple fields from the dataset so I need to use a formula, to create the three up/down/side arrows. Previously in Crystal Reports this could be implemented using a series of IF statements as follows. The below example is what is required for the down arrow. (the other 2 arrows also have multiple calculations)

        IF (({spScorecard_SLView;1.CATEGORY_ID} = 4) OR 
       ({spScorecard_SLView;1.CATEGORY_ID} = 25)) THEN 
        IF ({spScorecard_SLView;1.PM_3MM_NC_CNT}-{spScorecard_SLView;1.3MM_NC_CNT}) < 
        0 THEN 'Down Arrow'

        ELSE IF (({spScorecard_SLView;1.CATEGORY_ID} = 21) 
        OR({spScorecard_SLView;1.CATEGORY_ID} = 26) OR 
        ({spScorecard_SLView;1.CATEGORY_ID} = 41)) THEN
        IF ({spScorecard_SLView;1.CM_TOTAL_CNT}> 0) AND 
        (({spScorecard_SLView;1.PM_3MM_TOTAL_CNT} = 0) OR 
        ({spScorecard_SLView;1.3MM_TOTAL_CNT} = 0)) AND 
        ({spScorecard_SLView;1.3MM_NC_CNT} > 0) AND 
        (((({spScorecard_SLView;1.3MM_TOTAL_CNT} - {spScorecard_SLView;1.3MM_NC_CNT}) 
        / {spScorecard_SLView;1.3MM_TOTAL_CNT}) * 100) >= 0.00) THEN 'Down Arrow' //

        ELSE IF ((((({spScorecard_SLView;1.3MM_TOTAL_CNT} - 
        {spScorecard_SLView;1.3MM_NC_CNT}) / {spScorecard_SLView;1.3MM_TOTAL_CNT}) * 
        100) -((({spScorecard_SLView;1.PM_3MM_TOTAL_CNT} - 
        {spScorecard_SLView;1.PM_3MM_NC_CNT}) / 
        {spScorecard_SLView;1.PM_3MM_TOTAL_CNT}) * 100))/100) < 0.00 THEN 
        'Down Arrow'

I am stuck as to how to do something similar in PBI Report builder. Should I create a formula in the Value field under Value and States, and then delete any arrow settings under the Indicator States? Can you create a formula using 'Down Arrow' etc in an IIf statement? I can only get indicator data returned when selecting 1 field under Value, but I need multiple fields & conditions.

SSRS Reports are similar to PBI Report builder so if there are any examples using it that may be of help. I am connecting to a SQL Server stored proc to pull back the data.

Thanks Blowers

Answer 1

I ended up using IIF Logic to solve this, some of the calculations were too awkward in the end and it was easier for me to use IIF. Using the 1,2,3 indicator values works well.

Here is the expression that i ended up using:

  =IIF(Fields!CATEGORY_ID.Value = 4 AND Sum(Fields!PM_3MM_NC_CNT.Value - 
  Fields!Q3MM_NC_CNT.Value) < 0,1,
  IIF(Fields!CATEGORY_ID.Value = 25 AND Sum(Fields!PM_3MM_NC_CNT.Value - 
  Fields!Q3MM_NC_CNT.Value) < 0,1,
  IIF(Fields!CATEGORY_ID.Value = 4  AND Fields!PM_3MM_NC_CNT.Value <> 0 
  AND Fields!Q3MM_NC_CNT.Value <> 0 AND Fields!PM_3MM_NC_CNT.Value = 
  Fields!Q3MM_NC_CNT.Value ,2,
  IIF(Fields!CATEGORY_ID.Value = 25 AND Fields!PM_3MM_NC_CNT.Value <> 0 
  AND Fields!Q3MM_NC_CNT.Value <> 0 AND Fields!PM_3MM_NC_CNT.Value = 
  Fields!Q3MM_NC_CNT.Value ,2,
  IIF(Fields!CATEGORY_ID.Value = 4 AND Sum(Fields!PM_3MM_NC_CNT.Value - 
  Fields!Q3MM_NC_CNT.Value) > 0, 3,
  IIF(Fields!CATEGORY_ID.Value = 25 AND Sum(Fields!PM_3MM_NC_CNT.Value - 
  Fields!Q3MM_NC_CNT.Value) > 0, 3,
  IIF(Fields!CATEGORY_ID.Value = 4 AND Fields!PM_3MM_NC_CNT.Value = 0 AND 
  Fields!Q3MM_NC_CNT.Value = 0,4,
  IIF(Fields!CATEGORY_ID.Value = 25 AND Fields!PM_3MM_NC_CNT.Value = 0 
  AND Fields!Q3MM_NC_CNT.Value = 0,4,
  IIF(Fields!CATEGORY_ID.Value = 21 AND Fields!CM_TOTAL_CNT.Value > 0 AND 
  Fields!PM_3MM_TOTAL_CNT.Value =0,1, 
  IIF(Fields!CATEGORY_ID.Value = 26 AND Fields!CM_TOTAL_CNT.Value > 0 AND 
  Fields!PM_3MM_TOTAL_CNT.Value =0,1, 
  IIF(Fields!CATEGORY_ID.Value = 41 AND Fields!CM_TOTAL_CNT.Value > 0 AND 
  Fields!PM_3MM_TOTAL_CNT.Value =0,1, 
  IIF(Fields!CATEGORY_ID.Value = 21 AND Fields!Q3MM_TOTAL_CNT.Value = 0 
  AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - 
  Fields!TrendCalculation2.Value > 0.00,1, 
  IIF(Fields!CATEGORY_ID.Value = 26 AND Fields!Q3MM_TOTAL_CNT.Value = 0 
  AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - 
  Fields!TrendCalculation2.Value > 0.00,1, 
  IIF(Fields!CATEGORY_ID.Value = 41 AND Fields!Q3MM_TOTAL_CNT.Value = 0 
  AND Fields!Q3MM_NC_CNT.Value > 0 AND Fields!TrendCalculation1.Value - 
  Fields!TrendCalculation2.Value > 0.00,1, 
  IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 
  AND Fields!PM_3MM_TOTAL_CNT.Value = 0,3,
  IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 
  AND Fields!Q3MM_TOTAL_CNT.Value = 0,3,
  IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 
  AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value < 
  0.00,1,
  IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 
  AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value > 
  0.00,3,
  IIF(Fields!CATEGORY_ID.Value <> 12 AND Fields!CATEGORY_ID.Value <> 30 
  AND Fields!TrendCalculation1.Value - Fields!TrendCalculation2.Value = 
  0.00,2,
  5)))))))))))))))))))

Answer 2

I would approach it like this...

Set a formula in the Indicator Value so that you return a number that corresponds to the arrow you want to show (e.g. return 1, 2 or 3)

You can use whatever format you feel comfotable with but I would suggest using the SWITCH() function rather than nested IIFs.

For example this checks two fields and returns one of three values, (this is just a random example to illustrate the point)

=SWITCH(
SUM(Fields!Amount.Value) >5000 AND Fields!Year.Value >2019, 1
, SUM(Fields!Amount.Value) >10000 AND Fields!Year.Value <2019, 2
, True, 3
)

Switch takes pairs of expressions and return values. It returns the value when it hits the first expression that evaluates to True. So this reads...

• If the aggregated Amount is greater than 5000 and the Year >2019 then return 1
• If the aggregated Amount is greater than 10000 and the Year <2019 then return 12
• Else return 3

The final 'True', as it will always return true acts like an ELSE

Anyway, this will return a value of either 1, 2 or 3

Then in the Indicator Properties, just set the range for each indicator to 1, 2 or 3 like this