Leverage from a new measure a KPI (Status) created on Tabular Editor?

Power BI Online Service
1

Hi there team,
I hope everybody is safe!
I’ve just learnt and started to use KPI features on Tabular Editor for PowerBi and the feature is just awesome.
The biggest issue I have now is that I want to leverage for example Status or Trend from another measure, but PowerBi doesn’t see any of these [_MeasureName Status] .
Is there any way, trick or best practice that I’m not aware of and I can leverage to make that work outside the KPI itself?
Need to for example leverage a slicer to dynamically select the values AND the status AND the trend of a couple of KPIs , although I can only see the value but not the rest (status for example).

here´s the KPI
image

and here´s the code that´s not working (see KPI Status is greyed out)

image
image1002×197 7.31 KB

thanks in advance for all your help as always !
stay safe
Daniel

2

Hello @dsiffredi,

Thank You for posting your query onto the Forum.

I’m providing a link of an article which also contains a video of SQLBI where recently they’d created a content on this topic.

Hoping this article helps you to achieve the desired result that you’re looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

3

yeah thanks, but that doesn’t cover the issue. I saw that video and created the KPIs - but I need to be able to leverage status from another measure and PBI doesn’t see it

4

Does it apply to you?
SQL Server Analysis Services, Azure Analysis Services or Power BI Premium

5

Hi @dsiffredi , we’ve noticed that no response has been received from you since the 20th of August. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!

6

Thanks for checking. Short answer is No. nobody came with an explanation to answer my question.
The question is very simple. How do I read the status of a calculation group from another measure. It doesn’t seem impossible but I can’t find a how to do that.

7

Hi @dsiffredi, we’ve noticed that you posted on an inactive post and previously tagged solved topic. For more visibility please start a new topic within the forum. You may check this how-to guide for reference - How To Use The Enterprise DNA Support Forum https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951

8

@dsiffredi I was able to see what you mean! I was able to see the measure in DAX Studio and use it, but I could not use it in Power BI Desktop.

KPI Measure
KPI Measure1287×1050 113 KB

How annoying! I don’t really see a way around this directly. (I did try to force it through tabular editor but got an error).

I had to re-create the status expression as it’s own stand alone measure outside of the KPI to call it in another measure within Power BI Desktop. Then I could use it like you were trying to do here.

KPI Details
KPI Details1525×284 84.4 KB

KPI Measure Explicit
KPI Measure Explicit1490×144 14.2 KB

At this point I’d probably just rather do the whole KPI in Power BI Desktop (simply creating a status measure then formatting it to use shape only, for example).

1 Like
9

Hi @dsiffredi, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

However, for better visibility of post, we suggest creating a new thread. Thanks!"

10

Hi there, I got it to work :smiley:
For the Target expression, I used a Switch measure to choose the selected measure
SWITCH( TRUE(),
[Metric Selected] = “Revenue”, [AVG Daily Sales Overall],
[Metric Selected] = “Costs”, [AVG Daily Costs Overall],
[Metric Selected] = “Profits”, [AVG Daily Profits Overall], [Total Sales])

For the Status, I again used Switch
VAR DailySales = [Total Sales]
VAR DailyCosts = [Total Costs]
VAR DailyProfits = [Total Profits]
VAR SalesTolerance = 0.02
VAR SalesGoal = [_Total Sales Goal]
VAR CostsGoal = [_Total Costs Goal]
VAR ProfitsGoal = [_Total Profits Goal]
RETURN
SWITCH (
TRUE (),
[Metric Selected] = “Revenue”,
IF (
NOT ISBLANK ( DailySales ),
SWITCH (
TRUE,
DailySales < SalesGoal - SalesTolerance, -1,
DailySales > SalesGoal + SalesTolerance, 1,
0
)
),
[Metric Selected] = “Costs”,
IF (
NOT ISBLANK ( DailyCosts ),
SWITCH (
TRUE,
DailyCosts < CostsGoal - SalesTolerance, 1,
DailyCosts > CostsGoal + SalesTolerance, -1,
0
)
),
[Metric Selected] = “Profits”,
IF (
NOT ISBLANK ( DailyProfits ),
SWITCH (
TRUE,
DailyProfits < ProfitsGoal - SalesTolerance, -1,
DailyProfits > ProfitsGoal + SalesTolerance, 1,
0
)
)
)

and for the Trend:
VAR TotalSales = [Total Sales]
VAR PrevSales =
CALCULATE ( [Total Sales], PREVIOUSDAY ( ‘Dates’[Date] ) )
VAR TotalProfits = [Total Profits]
VAR PrevProfits =
CALCULATE ( [Total Profits], PREVIOUSDAY ( ‘Dates’[Date] ) )
VAR TotalCosts = [Total Costs]
VAR PrevCosts =
CALCULATE ( [Total Costs], PREVIOUSDAY ( ‘Dates’[Date] ) )
RETURN
SWITCH (
TRUE (),
[Metric Selected] = “Revenue”,
IF (
NOT ISBLANK ( TotalSales ) && NOT ISBLANK ( PrevSales ),
SWITCH (
TRUE,
TotalSales > PrevSales, 1,
– Negative
TotalSales < PrevSales, -1,
– Positive
0
)
),
[Metric Selected] = “Costs”,
IF (
NOT ISBLANK ( TotalCosts ) && NOT ISBLANK ( PrevCosts ),
SWITCH (
TRUE,
TotalCosts > PrevCosts, 1,
– Negative
TotalCosts < PrevCosts, -1,
– Positive
0
)
),
[Metric Selected] = “Profits”,
IF (
NOT ISBLANK ( TotalProfits ) && NOT ISBLANK ( PrevProfits ),
SWITCH (
TRUE,
TotalProfits > PrevProfits, 1,
– Negative
TotalProfits < PrevProfits, -1,
– Positive
0
)
)
)

1 Like
11

Hi Sue, would you please attach the PBIX example? So I can download it and test it ?
thanks for taking the time to get to this point.
Daniel

12

Thanks datazoe , the fact that you took the time to test this out means a lot. I thought it was my limitation to get this to work but it looks like its a product limitation for now. Agree with your statement, if we have to recreate it all twice , then why use tabular editor in first place?

13

Management Insights - SB.pbix (563.4 KB)

1 Like