Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtered SWITCH with blank values

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 mo...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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.  

     

    SiteUserWanted
    1ac
    1bc
    1cc
    1dc
    2cc
    2bc
    2 c
    3dd
    3bd
    4ad
    4dd
    5aUnconfirmed
    5eUnconfirmed
    6 Unconfirmed
    7cc
    8eUnconfirmed

     

    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's avatar
      v-juanli-msft
      Community Support
      6 years ago

      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.