Looking for a Dax Measure which can solve my use case

[Anonymous]

Hi, 

I am trying to find a way to solve this but stuck , can someone help me out in solving this.

I have a table which has 4 columns shown in the picture. I want to count the no.of servers

Conditions has to consider:

• For each year I need to count the no.of servers that have cloud status as “ public.”
• Check if the server is used by any other app id in upcoming year and if it is not used then get the count of the servers for that year.

Final o/p expecting:

Ex:      year        server count

           2019         0

      <= 2020         1 (server 6)

      <= 2021          3 (server 6, server 7, server 5)

      <= 2022           4 (server 6, 7, 5, 1)

I try to write a measure which was not a good approach but end up as below:

Count =

var PublicCloudServer = SUMMARIZECOLUMNS('servers list'[server Name], FILTER('servers list','servers list'[cloud]= "public" && 'servers list'[date] <= 2022))

var Filter1 = FILTER('servers list','servers list'[cloud]= "public" && 'servers list'[date] <= 2022)

var OtherServerList = SUMMARIZECOLUMNS('servers list'[server Name], EXCEPT('servers list', p))

var UniqueServerList = except( o, o1)

return COUNTROWS(o2)

please let me know if there is any diffrent way to solve this.

Thanks.

[v-shex-msft]

Hi @Anonymous ,

>>Check if the server is used by any other app id in upcoming year and if it is not used then get the count of the servers for that year.

I still not so clear for your conditions, can you please explain this more clearly?

For example:

2020 has three public servers, why you say only server 6 suitable for these conditions? If you mean it need to recursive calculate with next records, current power bi not support recursive calculation in dax formula.

Regards,
Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thanks for responding.

my use case is to calculate the server count for the apps which are moving to public cloud ( cloud status "public").

Let me take 2020 year as example:
2020 has 3 public servers (server 6, 2, 4) but server ( 2 & 4 ) are used by other apps (i.e. app id 5, 7, 8, 9 ) in 2021, so I can't consider those servers are decommissioned in 2020. only (server 6) in 2020 was not used by any other apps in the following years so we can count (server 6) as decommissioned in 2020.
Let me know if you understand my use case or else I will try to put it in a different way.

Thanks.

[parry2k]

@Anonymous can you share data in excel file using link to onedrive/google drive to work on the solution.

[Anonymous]

Hi @parry2k ,

appId  serverName  cloud  date

1	server 1	public	2019
1	server 1	public	2019
1	server 1	public	2019
2	server 3	private	2019
3	server 6	public	2020
3	server 2	public	2020
3	server 4	public	2020
4	server 3	private	2020
5	server 4	private	2021
6	server 3	private	2021
7	server 4	private
7	server 2	private
7	server 3	private
7	server 4	private
7	server 4	private
8	server 2	public	2021
8	server 5	public	2021
8	server 5	public	2021
8	server 3	public	2021
8	server 7	public	2021
9	server 3	public
9	server 4	public
9	server 1	public
9	server 2	public
9	server 3	public
10	server 1	public	2022
10	server 1	public	2022

will this  Table data work  ?

Thanks for responding.

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can try to use following measure formula if it works:

decommissioned count = 
VAR currYear =
    MAX ( T1[Year] )
VAR currList =
    CALCULATETABLE (
        VALUES ( T1[Server Name] ),
        FILTER ( ALLSELECTED ( T1 ), [Status] = "public" ),
        VALUES ( T1[Year] )
    )
VAR nextlist =
    CALCULATETABLE (
        VALUES ( T1[Server Name] ),
        FILTER ( ALLSELECTED ( T1 ), [Year] > currYear )
    )
RETURN
    IF (
        currYear <> BLANK ()
            && ISINSCOPE ( T1[Year] ),
        COUNTROWS ( EXCEPT ( currList, nextlist ) )
    )

On you expect result 2,3 exclude from 2021, I think they are suitable for your conditions.(they haven't appeared in next year)

Regards,

Xiaoxin Sheng

[Anonymous]

Hi @v-shex-msft ,

Thanks for your time. very helpful, your Dax Measure works for my use case.

Thanks once again