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

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
6 years ago

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'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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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 ()
        )