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
qmartiny
Helper III
Helper III

COMBINEVALUES ignore blank

Hello,

 

(I have the following COMBINEVALUES that concatenates multiple flags into one field to get all the flags in one place. I added a UNICHAR for line break and it works fine, however still shows a blank line when the flag is blank.

How could I write this so that it only combines the non blank flags?

 

Thanks a lot for your help

 

qcheck total flag text =

VAR dealsize = IF (
    '010 - Pipeline'[qcheck1 avg dealsize] = 0,
    "ACV < Avg Region/Solution",BLANK())
VAR dealscore =     IF (
        '010 - Pipeline'[qcheck3 dealscore] = 0,
        "Deal Score < 56%",BLANK())
VAR R4Q =   IF (
            '010 - Pipeline'[qcheck2 4rq] = 0,
            "Closing outside R4Q",BLANK())
VAR owner = IF ( '010 - Pipeline'[qcheck4 oppowner] = 0, "DME Opp Owner", BLANK() )


RETURN
COMBINEVALUES(UNICHAR ( 10 ), dealsize , dealscore , R4Q , owner)
1 ACCEPTED SOLUTION

How about:

qcheck total flag text = 
VAR dealsize = 1
VAR dealscore = BLANK ()
VAR R4Q = 3
VAR owner = 4

VAR FlagList = 
    FILTER (
        {
            dealsize,
            dealscore,
            R4Q,
            owner
        },
        NOT ISBLANK([Value])
    )

VAR Result =
    CONCATENATEX (
        flaglist,
        [Value],
        "|"
    )

RETURN
    Result


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

6 REPLIES 6
qmartiny
Helper III
Helper III

Hi @bcdobbs 

 

Thank you so much for your reply!

It looked promising however maybe because I am using UNICHAR(10) as a line break still shows 'blank lines'

 

I tried with a separator instead and it works, however it keeps the separator in the string ex 

|||drivers||

Obivously expected would be to only keep the values non blank.. I tried to use FILTER/ISNONBLANK without luck,

 

Appreciate the help again!

 

Thank you


Quentin

Hi Quentin,

Are you just using it in a card visual? Could you send a screen shot of the output? I didn't get blank lines with my test measure. 

Will do some more testing my end as well.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

I am using it in a Matrix, as 'Values' example with | separator

qmartiny_0-1658942952490.png

 

With UNICHAR

 

qmartiny_2-1658943042883.png

 

 

How about:

qcheck total flag text = 
VAR dealsize = 1
VAR dealscore = BLANK ()
VAR R4Q = 3
VAR owner = 4

VAR FlagList = 
    FILTER (
        {
            dealsize,
            dealscore,
            R4Q,
            owner
        },
        NOT ISBLANK([Value])
    )

VAR Result =
    CONCATENATEX (
        flaglist,
        [Value],
        "|"
    )

RETURN
    Result


Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Legend.


Thank you so much for your help! It's humbling to see how good people are at this.


Have a great evening

 

Quentin

bcdobbs
Super User
Super User

I'd create a table variable with your existing variables and then use CONCATENATEX to iterate over the table which will ignore blanks:

(Put your existing logic back in for the original variables)

qcheck total flag text =
VAR dealsize = 1
VAR dealscore = BLANK ()
VAR R4Q = 3
VAR owner = 4

VAR FlagList = 
	{
	    dealsize,
	    dealscore,
	    R4Q,
	    owner
	}

VAR Result =
    CONCATENATEX (
        flaglist,
        [Value],
        UNICHAR ( 10 )
    )

RETURN
    Result

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.