cancel
Showing results for
Did you mean:
Helper I

## DAX : IF with Relatedtable function help needed

Hi Team,

I need to calculate which client of mine is due for a consultation. There are rules set that you can see in the code like person earning less than 40K needs a consultation once a year, person earning between 40-70K needs to consult every 6 months, etc

I have this Calculated column that I want to remove and convert to a measure :

``````Due for Medical Consultation=
var DateDiff = (DATEDIFF(LASTDATE(Consultation[Consultation Date]),TODAY(),DAY))

Return

IF(
Client[Pay Group] = "Under 40K"
&& DateDiff >=365,
"Due",
IF(
Client[Pay Group] = "40-70k"
&& DateDiff >= 180,
"Due",
IF(
Client[Pay Group] = "Above 70K"
&& DateDiff >=90,
"Due"
)``````

Can anyone help me with writing a measure to support this?

1 ACCEPTED SOLUTION
Solution Sage

You want to achieve it with measures, right? I used the Client table as a dimension table, the Consualtaion table as fact table. Below measures for your reference:

LatestDate = IF([Due for Medical Consultation] = "Due", LASTDATE(Consulation[Consulattion Date]))

LatestResult =
IF([Due for Medical Consultation] = "Due",
VAR T1 = FILTER(Consulation,Consulation[Consulattion Date]=[LatestDate])
RETURN
MAXX(T1,[Result]))

Due for Medical Consultation =
VAR DateDiff =
( DATEDIFF ( LASTDATE ( Consulation[Consulattion Date] ), TODAY (), DAY ) )
VAR CurGroup =
SELECTEDVALUE ( Client[Pay Group] )
RETURN
SWITCH (
TRUE (),
CurGroup = "Under 40K"
&& DateDiff >= 365, "Due",
CurGroup = "40-70k"
&& DateDiff >= 180, "Due",
CurGroup = "Above 70K"
&& DateDiff >= 90, "Due",
BLANK ()
)

6 REPLIES 6
Post Patron

Is there a reason you want to convert to a measure and not keep it as a calculated column?  Are you not getting the desired result?

Helper I

Considering the tables i have (See my reply above) i don't think this data belongs in any table. I would rather have it stored separately as a measure. Even from a performance standpoint, it should decrease refresh time, no?

Super User III

Hi,

Share some data and clearly show the buckets of income for consultation frequency.  Please also show the expected result on the source data that you share.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

@Ashish_Mathur , i created sample data in excel to show you the tables I have and the output visual table I want :

Solution Sage

You want to achieve it with measures, right? I used the Client table as a dimension table, the Consualtaion table as fact table. Below measures for your reference:

LatestDate = IF([Due for Medical Consultation] = "Due", LASTDATE(Consulation[Consulattion Date]))

LatestResult =
IF([Due for Medical Consultation] = "Due",
VAR T1 = FILTER(Consulation,Consulation[Consulattion Date]=[LatestDate])
RETURN
MAXX(T1,[Result]))

Due for Medical Consultation =
VAR DateDiff =
( DATEDIFF ( LASTDATE ( Consulation[Consulattion Date] ), TODAY (), DAY ) )
VAR CurGroup =
SELECTEDVALUE ( Client[Pay Group] )
RETURN
SWITCH (
TRUE (),
CurGroup = "Under 40K"
&& DateDiff >= 365, "Due",
CurGroup = "40-70k"
&& DateDiff >= 180, "Due",
CurGroup = "Above 70K"
&& DateDiff >= 90, "Due",
BLANK ()
)

Super User III

Hi,

I just cannot understand your requirement.  Someone else will help you.  Sorry.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Sign up for our May 4th event!

May the fourth be with you, join us online!

#### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors