Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
)
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |