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
elia22gr
Helper II
Helper II

Measures not appearing in text box or smart narratives visual & cannot change their data format

Hi all,

 

I want to make a dynamic subtitle to my charts and I want to use text boxes because they are more flexible in formattings. But I am facing the below problems.

 

In card visual my measures works fine but in text box or smart narratives visual they are not working (not showing anything). Also, something that I noticed is that when I am trying to change the data format of my measures from General to Text (as they should be) it doesn't work (I tried both from Measures Format in Report View and in Modeling View). The outcome is text for sure in both cases (the columns that I take from the result are Text too). My measures are similar to this:

Sub_1 =

VAR MonthStatus = MAX('Calendar_Table'[Status])

VAR SelectedmonthTarget = MAX('Calendar_Table'[TargetName])

VAR LastaMonth = CALCULATE(MAX('Calendar_Table'[Month_No]),FILTER(ALL('Calendar_Table'),'Calendar_Table'[Status]="a"))

VAR LastaMonthTarget = CALCULATE(MAX('Calendar_Table'[TargetName]),FILTER(ALL('Calendar_Table'),'Calendar_Table'[Month_No]=LastaMonth))

VAR Result =

IF(

    MonthStatus = "a", SelectedmonthTarget,

    LastaMonthTarget

)

RETURN

Result

I have also tried the FORMAT and CONVERT DAX function to change the result of measures into text once more but I had no luck with both of my problems.


Have any idea why these are happening?

 

Best Regard,

Eleftheria

6 REPLIES 6
CBO2404
Helper I
Helper I

I have the same problem. A month ago this still worked and I used measures and text in my text visual. Recently I only see blanks.

v-shex-msft
Community Support
Community Support

HI @elia22gr,

I suppose this formula will return two different data type based on if statement conditions, right? AFAIK, current power bi desktop does not support on field return multiple types. For this scenario, I think you may need to add processing to convert all the result to text before setting field type.

Sub_1 =
VAR MonthStatus =
    MAX ( 'Calendar_Table'[Status] )
VAR SelectedmonthTarget =
    MAX ( 'Calendar_Table'[TargetName] )
VAR LastaMonth =
    CALCULATE (
        MAX ( 'Calendar_Table'[Month_No] ),
        FILTER ( ALL ( 'Calendar_Table' ), 'Calendar_Table'[Status] = "a" )
    )
VAR LastaMonthTarget =
    CALCULATE (
        MAX ( 'Calendar_Table'[TargetName] ),
        FILTER ( ALL ( 'Calendar_Table' ), 'Calendar_Table'[Month_No] = LastaMonth )
    )
RETURN
    IF ( MonthStatus = "a", SelectedmonthTarget & "", LastaMonthTarget & "" )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi  @v-shex-msft ,

 

Thank you for your reply. I tried it but it is not working. The stange things are 2:

In card visual my measure works fine

The returning results are text (the column 'Calendar_Table'[TargetName] is Text. And I have tried the result variable with Convert and Format Dax Function and I had no luck. Furthermore, I cannot change the data format of my measure from General to Text.

 

Any ideas?

 

Thank you in advance,

Eleftheria

Hi @elia22gr,

Can you please share same dummy data to test? I will try it to confirm if this type formulas not suitable to use in smart narratives.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

 

I don't understand why I should use the VALUE function. VALUE converts a sting to number. My possible results have this format: BU, RU 1+11 etc these cannot be converted into numbers and they are text but nor the measure itself can take the data type text neither returns a result into a text box or smart narratives. It is only working into card visual.  

Hi @elia22gr,

Sorry for the value function and above sample code, I was garbling it and some other Dax functions features and usages. (I modified and remove these wrong parts)

As you said, if this only works on the card visual instead of smart narrative, you can consider to submit an idea for improvin smart narrative features.

Microsoft Power BI ideas

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.