Re: First & Second Instances has to be counted bas...

Anonymous · ‎06-12-2021

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_Code	Start of Support	Network_Function	Hostname	Site_Metal_Rating	Serial
AUAON	2/1/2016	Core Switch	AP-AUAON-C001-W-S.mdlz.net	Silver	FOC2117X1Y8
AUAON	11/19/2015	Router	MELGMNS9843061	Silver	FDO2105A0PS
AUAON	1/9/2016	SDWAN	CAUALT3-1308-SDWAN-1	Silver	00-1B-BC-16-55-C2
AULTO	1/7/2016	Core Switch	AP-AULTO-C001-W-S.mdlz.net	Bronze	FDO2201V0TU
AULTO	1/30/2016	Router	MELGMNS9295113	Bronze	FDO2105A0PR
AULTO	11/15/2015	SDWAN	CAULVT7-1308-SDWAN-1	Bronze	00-1B-BC-16-58-20
AUBKH	1/27/2016	Access Switch	AP-AUBKH-A002-O-S.ap.csplc.org	Silver	FDO1332Z0GQ
AUBKH	12/29/2015	Core Switch	AP-AUBKH-C001-O-H.mdlz.net	Silver	FCW1943D18V
AUBKH	11/26/2015	Core Switch	AP-AUBKH-C001-O-H.mdlz.net	Silver	FCW1943D1BA
AUBKH	11/2/2015	LWAP	AP-AUBKH-W001-O-O	Silver	FGL2206A5KZ
AUBEO	5/23/2015	Access Switch	AP-AUBEO-A001-O-S.ap.csplc.org	Silver	FDO1332Z0GP
AUBEO	7/14/2015	Core Switch	AP-AUBEO-C001-O-S.mdlz.net	Silver	FCW1943F0NS
AUBEO	8/10/2015	LWAP	AP-AUBEO-W003-O-O	Silver	FGL2206A5L3
AUCEE	3/9/2016	Core Switch	AP-AUCEE-C001-P-H.mdlz.net	Silver	FCW1943F0NK
AUCEE	4/21/2016	Firewall	AP-AUCEE-F001-E-H	Silver	FG100E4Q17027523
AUCEE	6/11/2016	Firewall	AP-AUCEE-F002-E-H	Silver	FG100E4Q17027399
AUCEE	6/28/2016	LWAP	AP-AUCEE-W003-P-O	Silver	FGL2206A5L6

Jihwan_Kim · ‎06-13-2021

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 )

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

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

Go to My LinkedIn Page

Anonymous · ‎06-13-2021

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