Forum Discussion
Help for Measure with Custom Rule DAX Formula
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
4 Replies
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.
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
Hi Anonymous ,
You could edit rank column with the ClientCode filter.
rank = RANKX ( FILTER ( 'Table', 'Table'[ClientCode] = EARLIER ( 'Table'[ClientCode] ) ), 'Table'[Date], , ASC, DENSE )
