Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 : 

 

UserVisitDateTypeOfVisitRuleNoGoPeriod
Av101/02/2019T22years NoGo01/02/2021
Bv201/01/2019T22years NoGo01/01/2021
Bv301/11/2018T11 year NoGo01/11/2019

 

Result 

UserNoGo Period
A01/02/2021
B01/11/2019

 

 

Thank you for your help.

 

Romain

4 REPLIES 4
Anonymous
Not applicable

Hi @v-eachen-msft 

 

Unfortunately it does not work as expected : 

 

Here what I get when I create my RANK 

 

ClientCodeUsersDateTypeRank
C1A11/04/2019 08:50T211341
C1B24/01/2019 10:00T141700
C1B13/12/2018 08:55T210202

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
v-eachen-msft
Community Support
Community Support

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.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

I'll try ! 

 

Thank you very much. 🙂 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.