Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DAX formula issue

Hey guys, 

 

I am having issues with  DAX formula, its a litte tough to explain, but I will try to make the question simple. 

 

Particularly, when I tried to get the list of UDER ID before, when Year=2020, i was doing this: Calculate(SUM(USER_ID), FY="2020"

 

But now my USER ID data item has letters in it as well, so SUM function does not work. It says that SUM function does not work with values of type string.

With the help of which other function I can get the list of the USER ID, when Fiscal year is 2020?

 

P.S. I dont need the count disticnt of the USER ID, I need the actual list of it when the Fiscal year is 2020. I also dont want to apply the filter, i want to have it all in the DAX formula. 

 

Thanks, 

Sona

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    You could create a measure like:

    measure =
IF ( MAX ( table[fy] ) = 2020, 1, 0 )

    Add column [user id] into a table visual,

    add the measure into visual levle filter of the table above, then select [meaure]=1 in the filter pane and apply it.

    finally, the table visual will only show user id which is from 2020.

     

    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.

8 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    6 years ago

    Anonymous 

    You can do a measure that will only return a value when the year = 2020, something like this.

    2020 count = CALCULATE(COUNTROWS(YourTable),YourTable[FY] = 2020)

    Then you put the names in the matrix and apply this measure as a filter on the matrix and set it to 'Is Not Blank' 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello jdbuchanan71 , 

       

      Thanks for your quick response. 

       

      USER ID is not the only identifer I have in the table, I also have applications, and one user can have a few applications. 

       

      Does this mean this function will get the count of application rows?

       

      Thanks, 

      Sona

      • jdbuchanan71's avatar
        jdbuchanan71
        Super User
        6 years ago

        What it will do is just count the rows in the table where the year is 2020.  If the year is not 2020 the measure will return blank so the rows would not show in your table.  Using the measure is just a way to filter what is shown in the table.

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

    Hi Anonymous 

    Try measure below:

    Calculate(MAX(Table[USER_ID]), filter(Table,Table[FY]="2020"))

    Table is the name of a table, [USER_ID] and [FY] are two columns' name.

     

    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

      Hey v-juanli-msft ,

      Thanks for your reply.

       

      I tried that, but it only gave me one value, I assume the MAX value,  but I need the list of the USER IDs. 

       

      Thanks, 

      Sona

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

        Hi Anonymous 

        You could create a measure like:

        measure =
IF ( MAX ( table[fy] ) = 2020, 1, 0 )

        Add column [user id] into a table visual,

        add the measure into visual levle filter of the table above, then select [meaure]=1 in the filter pane and apply it.

        finally, the table visual will only show user id which is from 2020.

         

        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.