cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KWelsh8144
Helper I
Helper I

Calculated Column

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

 

KWelsh8144_1-1664192016704.png

 

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!

 

1 ACCEPTED SOLUTION
v-mengzhu-msft
Community Support
Community Support

Hi @KWelsh8144 ,

 

Like this?

vmengzhumsft_0-1664264000255.png

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

 

View solution in original post

3 REPLIES 3
wdx223_Daniel
Super User
Super User

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”)

v-mengzhu-msft
Community Support
Community Support

Hi @KWelsh8144 ,

 

Like this?

vmengzhumsft_0-1664264000255.png

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

 

AnthonyJoseph
Resolver II
Resolver II

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"
)

 

AnthonyJoseph_0-1664262066581.png

 

I think this is what you are expecting...

 

Thanks,

AnthonyJoseph

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors