Help for Measure with Custom Rule DAX Formula

[Anonymous]

Hi everyone, 

I'm facing a problem with one of my customer as he wants to implement a quite tricky rule into our dashboard. 

Let me explain the case : 

I have the following data 

- User that do Visits of which have a Type.

- For each Visit we can calculate a No Go Period 

• For T2 ==> 2 Years
• For T1 => 1 Year

Specific Rule : 

• If User A has done a T2 visit after User B , then the No Go Period for UserB related to T2 visit should not be considered

What I want to have at the end

• A table with User and No Go Period

Example : 

User	Visit	Date	TypeOfVisit	Rule	NoGoPeriod
A	v1	01/02/2019	T2	2years NoGo	01/02/2021
B	v2	01/01/2019	T2	2years NoGo	01/01/2021
B	v3	01/11/2018	T1	1 year NoGo	01/11/2019

Result 

User	NoGo Period
A	01/02/2021
B	01/11/2019

Thank you for your help.

Romain

[Anonymous]

Hi @v-eachen-msft 

Unfortunately it does not work as expected : 

Here what I get when I create my RANK 

ClientCode	Users	Date	Type	Rank
C1	A	11/04/2019 08:50	T2	11341
C1	B	24/01/2019 10:00	T1	41700
C1	B	13/12/2018 08:55	T2	10202

(It is an other example taken from my real business application) 

To explain more the context of this request : 

- My source is a Excel sheet with hundreds of visits from differents Users for different clientCode

- In my Dashboard I have a slicer for the clientcode to select the clientcode I'm interested in.

- So I can see all the visits linked to this client

Then I want to apply the logic I've described earlier to get the no go period of each user for this client. 

Here I should have : 

C1	B	1 Year No Go because T1 ( as the Type 2 No go as been cancelled by the T2 visit of A)	24/01/2020
C1	A	2 Year No Go because T2	11/04/2021

Moreover the measure I've been created following you instructions does not work as expected (maybe because I forgot to mention the above informations) : 'A table of multiple values was supplied where a single value was expected'

Maybe I have to 

• Order my visits in Power Query by clients and date
• Add new field in the measure? 

Thank you for your help 

Romain

[v-eachen-msft]

Hi @Anonymous ,

You could edit rank column with the ClientCode filter.

rank =
RANKX (
    FILTER ( 'Table', 'Table'[ClientCode] = EARLIER ( 'Table'[ClientCode] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

[v-eachen-msft]

Hi @Anonymous ,

You could use RANKX() to create an index firstly.

rank = 
RANKX('Table','Table'[Date],,ASC,Dense)

Then create a measure and the output is 1 or 0.

measure =
VAR a =
    LOOKUPVALUE (
        'Table'[User],
        'Table'[rank], SELECTEDVALUE ( 'Table'[rank] ) + 1
    )
VAR b =
    LOOKUPVALUE (
        'Table'[TypeOfVisit],
        'Table'[rank], SELECTEDVALUE ( 'Table'[rank] ) + 1
    )
RETURN
    IF ( a = "A" && b = "T2", 0, 1 )

 Now you could use filter to show result whose measure is 1.

[Anonymous]

I'll try ! 

Thank you very much. 🙂