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:
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 =
MonthStatus = "a", SelectedmonthTarget,
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?
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.
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 & "" )
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.
Thank you in advance,
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
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.
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.
Find out more about the May 2023 update.
Share your Data Story with the Community in the Data Stories Gallery.