topic Calculated Column in DAX Commands and Tips

Calculated Column

KWelsh8144 — Mon, 26 Sep 2022 11:39:07 GMT

I’m trying to set up a column that would be used as a flag, using conditionals.

I have an ‘informational’ table, and another table named ‘repeatable’. There is one record per record_id in my informational table, but multiple records per record_id in my repeatable table. It’s joining on record_id (one to many).

I need a conditional column created in my informational table, referencing a column (followupvisit) in my repeatable table.

It would go something like this….

if followupvisit has 0 only, then "first follow up visit completed",

if followupvisit includes a 0 and 1, then "2nd follow up visit completed",

if followupvisit includes a 0, 1, and 2, then "all follow up visits completed",

else “no follow up visits completed”.

Here's an example of my data

So for record_id 3, after I would create this new column, I’d have one row reflected here and it would say “2nd follow up visit completed” since there are 2 records in the repeatable table showing one with a 0 and one with a 1 in the followupvisit column. For records 4 and 5, it would say “no follow up visits completed”, since there weren’t any values in the followupvisit column in the repeatable table..

Can anyone assist me with the dax for this?

Thanks!

Re: Calculated Column

AnthonyJoseph — Tue, 27 Sep 2022 07:01:55 GMT

Hi KWelsh8144

I mocked up a table (persumed as information table in your case), can you please try the below code:

no of visits = var _maxvalue = CALCULATE(MAX('Table'[followupvisit]),FILTER( ALLEXCEPT('Table','Table'[id]),'Table'[id] = 'Table'[id])) return SWITCH(_maxvalue, 0, "first follow up visit completed", 1, "2nd follow up visit completed", 2, "all follow up visits completed", "no follow up visits completed" )

I think this is what you are expecting...

Thanks,

AnthonyJoseph

Re: Calculated Column

Anonymous — Tue, 27 Sep 2022 07:40:10 GMT

Hi KWelsh8144 ,

Like this?

you can create one measure and one calculated column to meet your needs:

sum = CALCULATE(SUM('Table'[follow up visit]),FILTER(ALL('Table'),'Table'[record id]=SELECTEDVALUE('Table'[record id])))

Column = IF([sum]=BLANK(),"no follow up visits completed",IF([sum]=0,"first follow up visit completed",IF([sum]=1,"2nd follow up visit completed","all follow up visits completed")))

Best regards,

Community Support Team Selina zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: Calculated Column

wdx223_Daniel — Tue, 27 Sep 2022 08:48:10 GMT

CalculatedColumn=SWITCH(CONCATENATEX(CALCULATETABLE(VALUES(repeatable[followupvisit])),repeatable[followupvisit],"",repeatable[followupvisit]),"0","first follow up visit completed","01","2nd follow up visit completed","012","all follow up visits completed",“no follow up visits completed”)