cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
galbatrox9
Helper I
Helper I

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?

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

 

Vera_33_0-1596980452675.png

 

View solution in original post

6 REPLIES 6
Dnerada
Post Patron
Post Patron

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? 

@Dnerada 

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
Super User III
Super User III

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

bxn8lPh

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

 

Vera_33_0-1596980452675.png

 

View solution in original post

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Kudoed Authors