Filtered SWITCH with blank values

[Anonymous]

The site column allows for duplicate values and I need the resulting values to be duplicated for each site.  The user column allows mixed and blank values.  I need the wanted column to reflect the most important values found in the user column.  

I have user and site columns and want to get a formula in order to calculate the "Wanted" column. 

The goal is to have a pie chart to show the count of real sites broken into the groups that work on them.  I hope to do this while respecting the individuality of each site.

Site	User	Wanted
1	a	a
1	b	a
1	c	a
1	d	a
2	c	b
2	b	b
2		b
3	d	b
3	b	b
4	a	a
4	c	a
5	d	d
5	e	d
6		Unconfirmed
7	c	c
8	e	e

[v-juanli-msft]

HI @Anonymous 

Create columns

rank =
IF (
    [User] <> BLANK (),
    RANKX (
        FILTER (
            'Table',
            'Table'[Site] = EARLIER ( 'Table'[Site] )
                && 'Table'[User] <> BLANK ()
        ),
        [User],
        ,
        ASC,
        DENSE
    )
)


you wanted =
VAR r =
    CALCULATE (
        MIN ( 'Table'[User] ),
        FILTER (
            'Table',
            'Table'[Site] = EARLIER ( 'Table'[Site] )
                && 'Table'[rank] = 1
        )
    )
RETURN
    IF ( r = BLANK (), "Unconfirmed", r )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-juanli-msft 

Thank you for your reply.  I feel this is on the right track.  However, the [User] field is not ranked alphabetically.  I need something where I can create my own dictionary/assign ranks in a hard coded manner.  

Site	User	Wanted
1	a	c
1	b	c
1	c	c
1	d	c
2	c	c
2	b	c
2		c
3	d	d
3	b	d
4	a	d
4	d	d
5	a	Unconfirmed
5	e	Unconfirmed
6		Unconfirmed
7	c	c
8	e	Unconfirmed

I just re-drew the table to give arbitrary priority to the letters 'c' and 'd' over all other letters. Then the other letters would lose all value and count as 'Unconfirmed'.

I created a new column and came up with random alphabetical names in order to make your original formula work.  Is there a formula where I can do this in less columns? 

I originally thought that the SWITCH formula would accept the ordering of my query as a ranking system.  I am trying to find a query that will let me rank inside the formula.  The [User] field has over 400 unique values and is text based.  I am trying to select 4 of these unique values in the 'User' field and rank them.  Every other of the 400 remaining 'User' values will be considered 'Unconfirmed'.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Enter value(like c,d) which you'd like to use as priority into a table and keep the value on the order as you need.

then in Transform data, add a custom column from the "value" list and extract values.

close&&apply, create columns with dax

Column1 = IF([User]<>BLANK(),FIND([User],[Custom],1,0))

Column =
VAR m =
    CALCULATE (
        MIN ( [Column1] ),
        FILTER (
            'Table 4',
            'Table 4'[Site] = EARLIER ( 'Table 4'[Site] )
                && 'Table 4'[Column1] > 0
        )
    )
RETURN
    IF (
        m <> BLANK (),
        CALCULATE (
            MIN ( 'Table 4'[User] ),
            FILTER (
                'Table 4',
                'Table 4'[Site] = EARLIER ( 'Table 4'[Site] )
                    && [Column1] = m
                    && [Column1] > 0
            )
        ),
        "Unconfirmed"
    )

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Logic is not very clear to me.

Try a new column like

new column =
var _1 = minx(filter(table,[Site] =earlier([Site])),[User])
return
if(isblank(_1),"Unconfirmed",_1)

[Anonymous]

@amitchandak Thanks for your reply.  The problem I am running into is this.  I have the blanks that prevent me from having the ELSE statement at the end of my DAX.  If I say 

[Site] = A, "A", "Unconfirmed"

Then when I have 

Site	User	Wanted
1	A	A
1		Unconfirmed

 When what I really want is like this

Site	User	Un-Wanted
1	A	A
1		A
2		Unconfirmed