Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

First & Second Instances has to be counted based on multiple conditions

Dear All, I have a devices related data, and in that need to find the First & Seconds instances of Support Start. I tried many ways and searched on the net for solution but couldn't find the answe...
Need Help
Show and Tell
Tips and Tricks
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, Anonymous 

I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below for creating measures.

 

 

Instance Rank Measure =
RANKX (
ALLEXCEPT ( 'Table', 'Table'[IPAM_Code] ),
CALCULATE ( MAX ( 'Table'[Start of Support] ) ),
,
DESC
)

 

 

First Instance Measure =
VAR instancerank = [Instance Rank Measure]
VAR currentipamcode =
MAX ( 'Table'[IPAM_Code] )
VAR Firstinstance =
MAXX (
ADDCOLUMNS (
FILTER ( ALL ( 'Table' ), 'Table'[IPAM_Code] = currentipamcode ),
"@rank", [Instance Rank Measure]
),
[@rank]
)
RETURN
IF ( instancerank = Firstinstance, 1 )
 
 
Second Instance Measure =
VAR instancerank = [Instance Rank Measure]
VAR currentipamcode =
MAX ( 'Table'[IPAM_Code] )
VAR Firstinstance =
MAXX (
ADDCOLUMNS (
FILTER ( ALL ( 'Table' ), 'Table'[IPAM_Code] = currentipamcode ),
"@rank", [Instance Rank Measure]
),
[@rank]
)
RETURN
IF ( instancerank = Firstinstance-1, 2 )
 
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello Jihwan_Kim,

    Thanks a lot for the reply.
    Yes you got it correct and it is giving the required results also.
    There is one query that may I get first & second instances for any specific "Network Funtion" like, Access Switch"
    As right now with these measures, when I filter a specific Network Function, sometimes 1st instance visible, not 2nd because that is related to another Network Function.
    Kindly guide here, if I can give the condition in the DAX to calcualte the 1st & 2nd instance for any specific Network Function value.

    Best Regards,
    Brajpal Shishodia


    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, Anonymous 

      Please check the link down below.

      I modified measures to react to the slicer if there is.

       

      https://www.dropbox.com/s/rnt773lt1vslzch/b4pbi.pbix?dl=0 

       

      Hi, My name is Jihwan Kim.

       

      If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

       

      Linkedin: linkedin.com/in/jihwankim1975/

      Twitter: twitter.com/Jihwan_JHKIM