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, 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?

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

     

     

6 Replies

  • 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

    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?