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 answer.

 

 

First Instance = 

        COUNTROWS ( 
        FILTER (
            Sheet1,
            Sheet1[IPAM_Code] = EARLIER(Sheet1[IPAM_Code]) &&
            Sheet1[Network_Function] = EARLIER(Sheet1[Network_Function]) &&
            Sheet1[Hostname] = EARLIER(Sheet1[Hostname]) &&
            Sheet1[Site_Metal_Rating] = EARLIER(Sheet1[Site_Metal_Rating]) &&
            Sheet1[Serial] = EARLIER(Sheet1[Serial]) &&
            Sheet1[Start of Support].[Date] = EARLIER ( Sheet1[Start of Support].[Date] )
        )
    ) = 1

 

 

First Instance = 
IF (
    Sheet1[Start of Support]
        = MINX (
            FILTER (
                Sheet1,
                EARLIER ( Sheet1[Network_Function] ) = "Access Switch"
                    && Sheet1[IPAM_Code] = EARLIER ( Sheet1[IPAM_Code] )
            ),
            Sheet1[Start of Support]
        ),
    1
)


In first DAX formula, it is giving True for all the instances, however, in 2nd DAX, all Access Switch devices are not been included, not sure why.

Kindly have a look at the data, I have to include all the columns which may be more as this is sample data because filter can be applied. IPAM Code is the Unique Field by which First & Second instance is required.

Please help me in this as stuck from whole 1 day now and has to deliver the report by EOD today.

IPAM_CodeStart of SupportNetwork_FunctionHostnameSite_Metal_RatingSerial
AUAON2/1/2016Core SwitchAP-AUAON-C001-W-S.mdlz.netSilverFOC2117X1Y8
AUAON11/19/2015RouterMELGMNS9843061SilverFDO2105A0PS
AUAON1/9/2016SDWANCAUALT3-1308-SDWAN-1Silver00-1B-BC-16-55-C2
AULTO1/7/2016Core SwitchAP-AULTO-C001-W-S.mdlz.netBronzeFDO2201V0TU
AULTO1/30/2016RouterMELGMNS9295113BronzeFDO2105A0PR
AULTO11/15/2015SDWANCAULVT7-1308-SDWAN-1Bronze00-1B-BC-16-58-20
AUBKH1/27/2016Access SwitchAP-AUBKH-A002-O-S.ap.csplc.orgSilverFDO1332Z0GQ
AUBKH12/29/2015Core SwitchAP-AUBKH-C001-O-H.mdlz.netSilverFCW1943D18V
AUBKH11/26/2015Core SwitchAP-AUBKH-C001-O-H.mdlz.netSilverFCW1943D1BA
AUBKH11/2/2015LWAPAP-AUBKH-W001-O-OSilverFGL2206A5KZ
AUBEO5/23/2015Access SwitchAP-AUBEO-A001-O-S.ap.csplc.orgSilverFDO1332Z0GP
AUBEO7/14/2015Core SwitchAP-AUBEO-C001-O-S.mdlz.netSilverFCW1943F0NS
AUBEO8/10/2015LWAPAP-AUBEO-W003-O-OSilverFGL2206A5L3
AUCEE3/9/2016Core SwitchAP-AUCEE-C001-P-H.mdlz.netSilverFCW1943F0NK
AUCEE4/21/2016FirewallAP-AUCEE-F001-E-HSilverFG100E4Q17027523
AUCEE6/11/2016FirewallAP-AUCEE-F002-E-HSilverFG100E4Q17027399
AUCEE6/28/2016LWAPAP-AUCEE-W003-P-OSilverFGL2206A5L6

3 Replies

  • 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

      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

        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