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

     

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
galbatrox9
Helper I
6 years ago

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?