Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Specific Rule :
What I want to have at the end
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
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
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
)
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.
I'll try !
Thank you very much. 🙂
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |