Forum Discussion

galbatrox9's avatar
galbatrox9
Helper I
6 years ago
Solved

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, pers...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

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

     

     

galbatrox9's avatar
galbatrox9
Helper I
6 years ago

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

 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

Hi,

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