Conditional

[afrutos]

Hello everyone, I have a question that I can't solve.
In this table I have a column with IdAuthor, and another that is Author Category.
I need to calculate something like this:
If the IdAuthor is Director, Writer and Screenwriter, I need to put "Director and Screenwriter".
If the IdAuthor is Director then Director
However when I apply my formula with SWITCH clause it doesn't get it for when the IdAuthor is Director, Writer and Writer at the same time.
This is my formula

Can you help me? Thanks

[v-yiruan-msft · Accepted solution]

Hi @afrutos ,

You can create a calculated column as below, please find the details in the attachment.

Category = 
VAR _category =
    CONCATENATEX (
        FILTER (
            ALL ( 'Authors' ),
            'Authors'[IdAuthor] = EARLIER ( 'Authors'[IdAuthor] )
        ),
        [CategoryAuthor],
        ",",
        'Authors'[CategoryAuthor]
    )
RETURN 
    IF (
        _category = "Director,Screenwriter,Writer ",
        "Director and Screenwriter",
        'Authors'[CategoryAuthor]
    )

Best Regards

[afrutos]

Hi, thank you how did you create the colum "column"?
I need to do this step before

[v-yiruan-msft]

Hi @afrutos ,

Please right-click or select the ellipsis ... next to the table Author in the Fields pane, and select New column from the menu just as below screenshot. You can find more details in the following official documentation.

Tutorial: Create calculated columns in Power BI Desktop

Best Regards

[afrutos]

Yes I know how to create a new Column, but my question is what did you do in that Calculated Column.
I mean, you showed me the column Category, 
Could you please showed me the Column column?

[v-yiruan-msft]

Hi @afrutos ，

You can just ignore that calculated column. I created that calculated column to show the unique category that each author belongs to, and then each category is concatenate with a ",".

Column = 
CONCATENATEX (
    FILTER ( 'Authors', 'Authors'[IdAuthor] = EARLIER ( 'Authors'[IdAuthor] ) ),
    'Authors'[CategoryAuthor],
    ",",
    'Authors'[CategoryAuthor]
)

Best Regards

[ryan_mayu]

@afrutos 

the Dax you highlighted does not work. You can try this

Column = 
VAR _d=maxx(FILTER('Table','Table'[IdAutor]=EARLIER('Table'[IdAutor])&&'Table'[CategoriaAtor]="Director"),'Table'[CategoriaAtor])
VAR _g=maxx(FILTER('Table','Table'[IdAutor]=EARLIER('Table'[IdAutor])&&'Table'[CategoriaAtor]="Guionista"),'Table'[CategoriaAtor])
VAR _a=maxx(FILTER('Table','Table'[IdAutor]=EARLIER('Table'[IdAutor])&&'Table'[CategoriaAtor]="Argumentista"),'Table'[CategoriaAtor])
return if(_a<>""&&_g<>""&&_d<>"","Director and Screenwriter")

[amitchandak]

@afrutos , a new column like

New column =
var _1 = countx(filter(Table, [IdAuthor] =earlier([IdAuthor]) && [CategoryAuthor] in {"Director","Writer","Screenwriter"} ) ,[IdAuthor])+0
var _2 = countx(filter(Table, [IdAuthor] =earlier([IdAuthor]) && [CategoryAuthor] in {"Director"} ) ,[IdAuthor])+0
return
switch(true(),
_1=3, "Director and Screenwriter",
_2 =1 , "Director",
[CategoryAuthor]
)

[afrutos]

Hi, i'm sorry but it's still not working
Because for exaple for the first one I should have "Director and Screenwriter"