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

Dyanmic Title card help

Hello, I made a dynamic title card with the following switch function which should return a specific value based on what is selected, however the Title Card's measure does not seem to be working the way I had originally thought it would. What should happen is that if something in the slicer is not selected the third option should show, If the something is selected in the filter then it should display option 2(which it does), and if they select more than one option the 1st message should appear. However currently, only the first two options react appropriately when I either: select more than one or select one, if I do not select anything in the slicer then it defaults to the first option of being greater than 2 which is not correct. The following is my measure did I make a mistake in my Switch Function? Location is a slicer.  

SWITCH(TRUE(),
DISTINCTCOUNT(Sheet1[Location])>=2,"Please select only one geography please",
DISTINCTCOUNT(Sheet1[Location])=1,"Displaying Numeric and Percent Change Results for "&VALUES(Sheet1[Location])&" by Industry Sector(except Total all Industries)",

DISTINCTCOUNT(Sheet1[Location])=0,"Displaying Numeric and Percent Change Results for New Jersey by Industry Sector(except Total all Industries)")

Currently the card will display the >=2 option as the default option even though the Distinctcount location column has nothing selected, but will function as intended if one thing is selected and will dynamically change to whatever was slected (i.e. If i hit New Jersey, it will fill in New Jersey and the same for New York).

This is strange because the >=2 option is functioning as a <>(does not equal)1 instead of actually accepting the math argument of selections being greater than 2, while completly ignoring the =0 option, which I would like to set as a default option if nothing is selected.

Thank you for your time,

Regards,

Microsoft Rookie.

1 ACCEPTED SOLUTION

Hello @v-shex-msft,

Xiaoxin,

Thank you for all your help; however I have found a solution to my problem. The snippet of code for the measure I used was 

Sample Measure =

VAR Location =
VALUES ( Sheet1[Location] )
VAR sample3 = COUNTROWS ( Location )
VAR NumberOfPossibleLocations =
COUNTROWS ( ALL ( Sheet1[Location] ))
VAR AllButLastSelectedLocation =
TOPN ( NumberOfPossibleLocations - 1, Location )
VAR LastSelectedLocation =
EXCEPT (Location, AllButLastSelectedLocation)
RETURN
"Sample Text 1 Showing results for "
& IF (
sample3= NumberOfPossibleLocations,
"sample text 2 ",
"For "
& IF (
sample3 = 1,
CONCATENATE(VALUES(Sheet1[Location])," This is determinate text and is used for one selection"),
IF(
sample3 =2,
"Select Only one please my guy",
IF(
sample3 = 3,
"Select Only one please my guy X3",
IF(
sample3 = 4,
"Select Only one please my guy X4",
IF(

sample3 = 5,
"Select Only one please my guy X5",
"DEFAULT TEXT HERE PLEASE"
)
& LastSelectedLocation
)))))

 

Once again thank you for all your help, your insight was useful in making this information, although I did confirm that the measure did not accept a > sign in places where sample3 = 4 would be, this is also an acceptable solution.

Regards,

Microsoftrookie

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @microsoftrookie,

 

First, I don't think you can use value range in switch function, '>2' condition may not work.
BTW, slicer can't distinguish all-selected and non-selected, your '=0' part is also invalid.

 

Maybe you can try to use below formula:

Title Measure=
VAR selected =
    SELECTEDVALUE ( Sheet1[Location] )
RETURN
    IF (
        selected <> BLANK (),
        "Displaying Numeric and Percent Change Results for " & selected
            & " by Industry Sector(except Total all Industries)",
        "Please select only one geography please"
    )

 

Regards,

Xiaoxin Sheng

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

Hello @v-shex-msft

Thank you for your quick reply Xiaoxin I apperaciate your help and tried out the formula that you had sent me.

 I had a similar function with an if statement using the distinctcount function that did the same thing that you posted earlier, though it was a bit sloppier measure that was:

Sample Measure=

IF(DISTINCTCOUNT(Sheet1[Location])=1,"Displaying Numeric and Percentage Change for Industries within " & VALUES(Sheet1[Location]),"Please select one Geography to view Results")

The formula that I am trying to create would allow for a dynamic title of 3 or more scenarios at once(in fact its what I am being asked to create), I thank you for your insight that the range function may not work in a measure it is a big help now that I am aware that measures may be limited in that way.

That being said, I've tried nesting an IF statement within an IF statement with the same logic utilizing the same switch functionality to no avail.

Do you think there may be any other solutions? If not, please let me know and again thank you for your time.

Regards,
Microsoftrookie

Hi @microsoftrookie,

 

>>Do you think there may be any other solutions? If not, please let me know and again thank you for your time.

Can you share some sample result which you expect?


Regards,

Xiaoxin Sheng

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

Hello @v-shex-msft,

Xiaoxin,

Thank you for all your help; however I have found a solution to my problem. The snippet of code for the measure I used was 

Sample Measure =

VAR Location =
VALUES ( Sheet1[Location] )
VAR sample3 = COUNTROWS ( Location )
VAR NumberOfPossibleLocations =
COUNTROWS ( ALL ( Sheet1[Location] ))
VAR AllButLastSelectedLocation =
TOPN ( NumberOfPossibleLocations - 1, Location )
VAR LastSelectedLocation =
EXCEPT (Location, AllButLastSelectedLocation)
RETURN
"Sample Text 1 Showing results for "
& IF (
sample3= NumberOfPossibleLocations,
"sample text 2 ",
"For "
& IF (
sample3 = 1,
CONCATENATE(VALUES(Sheet1[Location])," This is determinate text and is used for one selection"),
IF(
sample3 =2,
"Select Only one please my guy",
IF(
sample3 = 3,
"Select Only one please my guy X3",
IF(
sample3 = 4,
"Select Only one please my guy X4",
IF(

sample3 = 5,
"Select Only one please my guy X5",
"DEFAULT TEXT HERE PLEASE"
)
& LastSelectedLocation
)))))

 

Once again thank you for all your help, your insight was useful in making this information, although I did confirm that the measure did not accept a > sign in places where sample3 = 4 would be, this is also an acceptable solution.

Regards,

Microsoftrookie

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.