Forum Discussion

User232431's avatar
User232431
Helper III
1 year ago

Display dynamic selected value if value is missing

Hi , i am trying to show filtered text if it is missing.

In above example if i select "USA" then i can able display concatination of "City" and "Value"
if i select "UK" how can i display "City" and "(Blank)" (i.e in my example "A:(Blank)")
i have tried the below dax function 

Cards_Val =

IF(
    ISFILTERED(Sheet1[City]) ,
    SELECTEDVALUE( Sheet1[City])&" : "&AVERAGE(Sheet1[Value]),
    SELECTEDVALUE( Sheet1[City])&" : (Blank)"
    )
Thanks in advance.
Need Help

2 Replies

  • grazitti_sapna's avatar
    grazitti_sapna
    Super User
    1 year ago

    Hi User232431, Your DAX function logic is close, but it needs refinement to handle missing values dynamically. Below is the updated DAX formula that checks whether the Value field has data or not for the selected City and returns the appropriate concatenation:

    Cards_Val =
    IF (
    ISFILTERED(Sheet1[City]),
    SELECTEDVALUE(Sheet1[City]) & " : " & 
    IF (
    ISBLANK(AVERAGE(Sheet1[Value])),
    "(Blank)", 
    AVERAGE(Sheet1[Value])
    ),
    SELECTEDVALUE(Sheet1[City]) & " : (Blank)"
    )

    If I have resolved your question, please consider marking my post as a solution. Thank you!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi All,
    Firstly  grazitti_sapna thank you for your solution!
    And User232431 , I think the situation you need is when selecting UK it is (blank) + value right, here is what I did hope it helps!

    Cards_Val = 
IF(
    ISBLANK(SELECTEDVALUE(Sheet[Value])),
    SELECTEDVALUE(Sheet[City]) & " : (Blank)",
    IF(
        SELECTEDVALUE(Sheet[City])=BLANK(),
        "(Blank)" &":"& SELECTEDVALUE(Sheet[Value]),
        SELECTEDVALUE(Sheet[City]) & " : " & SELECTEDVALUE(Sheet[Value])
    )
)

     

    Hope it helps!

    Best regards,
    Community Support Team_ Tom Shen

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