Assistance with DAX formulas

[EZimmet]

Thank you in advanced for assisting being a Newbie this forum is fantastic TY for all your help.

I’m looking to populate 2 fields in a report _Client Count & _Rack Shared

This is Computer racks filled with Client devices the final report will need to populate the two columns above.

_Client Count Total number of Clients in the Rack

_Rack Shared will need to calculate if >=2 clients Yes else No

My attempt below

_Client Count =

CALCULATE(

                COUNTROWS('Assets'),'Assets[ParentID]

                     in VALUES('Rack'[ID]))

_Rack Shared =

if Value from (Racks[_Client Count] >= 2

                then "Yes"

                Else "No")

Table: Assets
ID	Client	Asset Tag	HDW-EQType	ParentID
685440	Client 1	ATAG:101010	Server	R001
685188	Client 1	ATAG:101011	Server	R001
900561	Client 2	ATAG:101012	Server	R001
712352	Client 2	ATAG:101013	Server	R001
685204	Client 3	ATAG:101014	Appliance	R001
685226	Client 3	ATAG:101015	Appliance	R001

Table: Rack
ID	Asset Tag	HDW-EQType	_Client  Count	_Rack Shared
R001	ATAG:Rack1	Rack	3	Yes

[philouduv · Accepted solution]

Hey @EZimmet ,

For the first column : 

_Client Count =

CALCULATE(DISTINCTCOUNT(Assets[Client]),

FILTER(All(Assets),

Assets[ParentID] == EARLIER(Assets[ParentID])))

(It will count for ech different Parent ID how many clients are associate with)


Then for the second column :

_Rack Shared = If(Assets[_Client Count] >=2,"Yes","No") 

(The second one is a simple IF use case , you could have find many examples on this forum or even the microsoft documentation IF function (DAX) - DAX | Microsoft Docs)

best regards,

[v-zhangti · Accepted solution]

Hi, @EZimmet 

You can try the following methods.

Column:

_Client Count = 
CALCULATE (
    DISTINCTCOUNT ( Assets[Client] ),
    FILTER ( Assets, [ParentID] = EARLIER ( Rack[ID] ) )
)

_Rack Shared = 
If([_Client Count] >=2,"Yes","No") 

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[philouduv · Accepted solution]

Hey @EZimmet ,

For the first column : 

_Client Count =

CALCULATE(DISTINCTCOUNT(Assets[Client]),

FILTER(All(Assets),

Assets[ParentID] == EARLIER(Assets[ParentID])))

(It will count for ech different Parent ID how many clients are associate with)


Then for the second column :

_Rack Shared = If(Assets[_Client Count] >=2,"Yes","No") 

(The second one is a simple IF use case , you could have find many examples on this forum or even the microsoft documentation IF function (DAX) - DAX | Microsoft Docs)

best regards,

[EZimmet]

Sorry for delay was on a work trip - TY