How do I create a Dynamic Report Title for the possible scenarios?

[HamidBee]

I am working with the following data:

Area	Values
A	45
B	34
C	87
D	56
E	97
F	34
G	21

I have plotted a simple bar chart to illustrate this data. I would like to have a dynamic title that does the following:

1) If none of the bars are selected it simply reads: "Total Number of Transactions".

2) If one bar is selected it reads: "Total Number of Transactions in Zone _". The underscore here would be replaced by the letter.

3) If two bars are selected it reads: "Total Number of Transactions in Zones _ & _".

4) If three or more bars are selected it reads "Total Number of Transactions in Zones _ , _ & _ ". And so on. 

Please find attached a link of the file which I am sharing here:

https://www.mediafire.com/file/npqmq5g0rm619xc/Sample_Data.pbix/file

Any help would be greatly appreciated. 

[Anonymous · Accepted solution]

Hi  @HamidBee ,

Here are the steps you can follow：

1. Create measure.

1&&7 =
var _discount=DISTINCTCOUNT(Data[Area])
var _max=MAX('Data'[Area])
return
SWITCH(
    TRUE(),
    _discount=7,"Total Number of Transactions",
    _discount=1,"Total Number of Transactions in Zone"&" "&_max)

2&&6 =
var _discount=DISTINCTCOUNT(Data[Area])
VAR _area = CONCATENATEX(
VALUES(Data[Area]),Data[Area],",")
var _len=LEN(_area)
var _replace=
REPLACE(_area,_len-1,1,"&")
return
"Total Number of Transactions in Zones " & _replace & ""

True =
var _discount=COUNT(Data[Area])
return
IF(
    _discount in {1,7},[1&&7],
    [2&&6])

2. Result:

Sovle1：

Sovle2：

Solve3：

Solve4：

Best Regards,

Liu Yang

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

[PijushRoy]

Hi @HamidBee 

Please try this

DynamicTitle = VAR _area = CONCATENATEX(

VALUES(Data[Area]),Data[Area],",")

return

SWITCH(

TRUE(),

COUNTA(Data[Area])=7, "Total Number of Transactions",

"Total Number of Transactions in Zones " & _area & "")

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[HamidBee]

I like the idea however the ampersands (&) are missing in conditions 3 and 4. 

[PijushRoy]

Hi @HamidBee 

You can change the text format as per your requirement.


If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos

[HamidBee]

Thank you but I Think the code will need some considerable adjustment inorder to achieve this. At the moment the code you have provided doesn't fall in line with my 3rd and 4th requirement. But thanks anyway.