Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to use something similar to CALCULATE with a string Column

Hi,

 

I need to do a filter very similar to this:

 

XXX = 

CALCULATE(
 SUM('All Activity '[NAME]),
 FILTER('All Activity ','All Activity '[TASK] = "Design Check"))

... but the column which I need to filter ('All Activity '[NAME]) is a column that contains STRINGS, same as the value that I need to obtain, so I can't use CALCULATE, or SUM but I'm completely ignorant of which options could I use.

I tried changing SUM by ALL, but I got an error that explains that I'm getting multiple values where there is only one value expected.
 
If someone can help me, would make my day 🙂
CALCULATE
Strings
  • VahidDM's avatar
    VahidDM
    4 years ago

    Hi Anonymous 

     

    You can add a new column to your table with this DAX:

    New Column = 
if('Table'[TASK]="Design Check",'Table'[NAME],blank())

     

    Output:

     

     

    or if you need Measure, use this:

    Measure = 
if(max('Table'[TASK])="Design Check",max('Table'[NAME]),BLANK())

     

    output:

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous 

     

    Can you post sample data as text and expected output?
    Not enough information to go on;

    please see this post regarding How to Get Your Question Answered Quickly:
    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.
    4. Relation between your tables

    Appreciate your Kudos!!
    LinkedIn:www.linkedin.com/in/vahid-dm/

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi VahidDM ,

     

    First of all thanks for your reply. I'll do my best to explain my issues.

     

    I have a Table with many columns, two of them that I need to work with are text Columns as in the example table 

     

    NAMETASKDATEEtc... 
    MariaDesign Check1/12/22Etc... 
    JhonDesign1/12/22Etc... 
    JhonDrafting7/12/22Etc... 
    MelissaDesign13/12/22Etc... 
    MelanieDesign Check13/12/22Etc... 

     

    I need a filter that, for example, return the name in Table[NAME] if Table[TASK] = "Design Check". 

     

    I've tried with some DAX like this, but without success:

    IF([Task Name]) = "Design Check", [Engineer Name], BLANK())
     
    [Task Name] and [Engineer Name] are metrics because for some reason the IF doesn't allow me to use the Table[Column] reference straight foward. I made them like this:
     
    Engineer Name = ALL('Table'[NAME])
     
    Usually, if I need to do this type of filter I'ld use the DAX that I used as an example in the first post, but since the column that I need to pull the info from is TEXT that filter does not work.
     
    THANKS in advance.
    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      Hi Anonymous 

       

      You can add a new column to your table with this DAX:

      New Column = 
if('Table'[TASK]="Design Check",'Table'[NAME],blank())

       

      Output:

       

       

      or if you need Measure, use this:

      Measure = 
if(max('Table'[TASK])="Design Check",max('Table'[NAME]),BLANK())

       

      output:

       

       

      If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
      Appreciate your Kudos!!
      LinkedIn:       www.linkedin.com/in/vahid-dm/

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Thanks VahidDM , you made my day!!! ðŸ˜‰