DAX : IF with Relatedtable function help needed

[galbatrox9]

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?

[Anonymous]

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? 

[galbatrox9]

@Anonymous 

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?

[Ashish_Mathur]

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.

[galbatrox9]

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

[Anonymous · Accepted solution]

Hi @galbatrox9 

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

[Ashish_Mathur]

Hi,

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