Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zzakir
Helper II
Helper II

Smart Narrative: If DAX results in no value in PowerBI

Hello,

I am trying to create a smart narrative and I was able to get most of the variables on my narrative except for the one in red which is favorable or unfavorable depending on a dax.

 

Narrative:

Budget for the month of May totaled $5M compared to actual expense of $1M, resulting a favorable/unfavorable variance of $4M

 

Dax:

Budget_Sentiment =
If([Budget Variance] < 0,
"unfavorable",
"favorable"
)
 
Note that when I put this Dax on a card in the same page, it works! but in a smart narrative it ends up with the hidden symbol Any idea on what may be happening here?
1 ACCEPTED SOLUTION

I think your issue is different. It may have something to do with that "N/A" as a result is text, whereas, _cmcost is a value.
Sugguestion, change the VAR to:

VAR _CMcost = CALCULATE(SUM(vw_AMS_Workorder[Total WO Cost]), vw_AMS_Workorder[Work Type (groups)] IN {"CM"}) && ""

 

 

Adding && "" will convert your _CMcost to a text value.
If that messes with your number format for display, you may have to look into wrapping the VAR  under "format()"

View solution in original post

13 REPLIES 13
v-eqin-msft
Community Support
Community Support

Hi @zzakir ,

 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution to make the thread closed. More people will benefit from it.

Hope to hear from you😀

 

Best Regards,
Eyelyn Qin

v-eqin-msft
Community Support
Community Support

Hi @zzakir,

 

Sorry for that the information you have provided is not making the problem clear to me.

I have built a data sample like this:

Budget Variance = SUM('Table'[Budget])- SUM('Table'[Actual Expense])
Budget_Sentiment = If([Budget Variance] < 0,"unfavorable","favorable")

set a flag.PNG

Actually I'm confused about your descriptions:

"If DAX results in no value"

"in a smart narrative it ends up with the hidden symbol "

 

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

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

@v-eqin-msft Hello Eyelyn,

Here is my measure:

zzakir_2-1624626690545.png

The measure works for me when I put it in a card or on a table but when I try to use it as a dynamic value it does not:

zzakir_3-1624626917599.png

 

I have circled the hide symbol I was refering to... I hope it was clear now.

Furthermore, when you hover over the symbol it says "Current data and filters produce no result for this value" which is clearly not true as you can see it does produce the result on a card visual.

I meant the "If" Dax/formula results in no value. I should've put "if" in quotes for clarity.

 

 

Hi @zzakir ,

 

Sorry, I'm still confused...

To my knowledge, the default value of measure shown in Card visual is the Aggregate value not the actual value:

Aggregate value.PNG

It is suggested share me with your pbix file after removing sensitive data and describe your expected output in detail to help us clarify your scenario.

 

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

Eyelyn,

The issue is not resolved. I am unable to attach the file like you did. Have you worked with using "dynamic values" in a text box? I think part of the reason you are having issues understanding the problem is due to that. I can't remove sensitive data as its a huge file. Lets try one more time:

 

I have something similar to what you have in the .pbix you attached to this thread. The issue is that when I add the measure as dynamic "value" to the text box it shows no value (note the screenshot below). 

zzakir_0-1625005644672.png

If I use the same measure in a card, it shows favorable/unfavorable depending on the filters applied.

zzakir_1-1625005823216.png

 

Why would the measure work in a card or table visual but not work as a dynamic "value"?

Hi @zzakir, Did you ever find why this is happening? I'm having the same issue currently where my measure will show a 0 in a card but have a hidden value in the smart narrative text box.

Are you using calculation groups? If so, are you using "SelectMeasure()" more than once in any one of your calculations?

That was the issue for me. I had to remove all the calculations in my calculation group that had "SelectMeasure()" reference more than once and have them as separate measure in PowerBI (as opposed to tabular editor).

 

Easy way to check this would be to make a copy of your PBIX and then delete the calculations in tabular editor that references "SelectMeasure()" more than once. Save changes and see if your dynamic values now show as they did on a card or another visual.

Hi @zzakir, I am not using SELECTMEASURE() at all in any calcs. I have simple DAX like this below:

WO Total Cost (CM) = 

VAR _CMcost = CALCULATE(SUM(vw_AMS_Workorder[Total WO Cost]), vw_AMS_Workorder[Work Type (groups)] IN {"CM"})

RETURN
IF(ISBLANK(_CMcost), "N/A", _cmcost)

This produces N/A in a card, but nothing in the smart narrative text box. 

I think your issue is different. It may have something to do with that "N/A" as a result is text, whereas, _cmcost is a value.
Sugguestion, change the VAR to:

VAR _CMcost = CALCULATE(SUM(vw_AMS_Workorder[Total WO Cost]), vw_AMS_Workorder[Work Type (groups)] IN {"CM"}) && ""

 

 

Adding && "" will convert your _CMcost to a text value.
If that messes with your number format for display, you may have to look into wrapping the VAR  under "format()"

@zzakir Ahh that was my issue! I had tried the previous DAX with a "0" as well, which also didn't work, but it's because I was making it text. When I replaced the "N/A" simply with 0, it works. 

Thank you for your quick replies! 

Happy to help. If this helped you, please mark it as a solution so it can help others navigate to it.

Did you find a solution to your problem?

 

I don't think I am able to mark as a solution as I did not post the original question. Maybe you can mark your own answer? 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.