cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
B4PBI
Frequent Visitor

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 3
Jihwan_Kim
Community Champion
Community Champion

Hi, @B4PBI 

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.

 

Picture1.png

 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

B4PBI
Frequent Visitor

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
Community Champion
Community Champion

Hi, @B4PBI 

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


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.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors