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 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.

 

 

SiteUserWanted
1aa
1ba
1ca
1da
2cb
2bb
2 b
3db
3bb
4aa
4ca
5dd
5ed
6 Unconfirmed
7cc
8ee

 

 

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.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

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

      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 

      SiteUserWanted
      1AA
      1 Unconfirmed

       When what I really want is like this

      SiteUserUn-Wanted
      1AA
      1 A
      2 Unconfirmed
  • 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.