Measure to count combinations

[Anonymous]

Hi Community,

I need help in creating a measure that considers the clients who have points 2 or null but not with a combination of 1 & 3:

So in the following dataset, client A,C,D,G & K 

Sample file:  https://1drv.ms/u/s!Ag919_pO_UKrgSy_A0tubIpz9H8G?e=F4A6Pe

Thanks!

[jdbuchanan71 · Accepted solution]

@Anonymous 
I think I got it to do what you are looking for.

Client Count = 
VAR _1Clients = CALCULATETABLE ( VALUES ('Table'[Client ID] ),'Table'[Points] = "1" )
VAR _3Clients = CALCULATETABLE ( VALUES ('Table'[Client ID] ),'Table'[Points] = "3" )
VAR _1and3 = INTERSECT ( _1Clients, _3Clients )
VAR _2orBlank = CALCULATETABLE ( VALUES ('Table'[Client ID] ), 'Table'[Points] IN { "2", "" } )
RETURN
    COUNTROWS ( EXCEPT ( _2orBlank,_1and3 ) )

[CNENFRNL]

Worksheet formula is enough to solve it

[Jihwan_Kim · Accepted solution]

Hi,

please check the below picture and the attached pbix file.

Result: =
COALESCE (
IF (
COUNTROWS (
INTERSECT (
CALCULATETABLE (
VALUES ( 'Table'[Points] ),
ALLEXCEPT ( 'Table', 'Table'[Date], 'Table'[Client ID] )
),
{ 1, 3 }
)
) = 2,
BLANK (),
SUMMARIZE (
FILTER ( 'Table', 'Table'[Points] IN { BLANK (), 2 } ),
'Table'[Client ID]
)
),
""
)

[jdbuchanan71 · Accepted solution]

@Anonymous 
I think I got it to do what you are looking for.

Client Count = 
VAR _1Clients = CALCULATETABLE ( VALUES ('Table'[Client ID] ),'Table'[Points] = "1" )
VAR _3Clients = CALCULATETABLE ( VALUES ('Table'[Client ID] ),'Table'[Points] = "3" )
VAR _1and3 = INTERSECT ( _1Clients, _3Clients )
VAR _2orBlank = CALCULATETABLE ( VALUES ('Table'[Client ID] ), 'Table'[Points] IN { "2", "" } )
RETURN
    COUNTROWS ( EXCEPT ( _2orBlank,_1and3 ) )

[amitchandak]

@Anonymous , Try a measure like

var _1 = calculate(sum(Table[Points]),filter(allselected(Table), Table[Client] =max(Table[client])))
return
sumx(filter( values(Table[client]), isblank(_1) || _1 =2 ), calculate(sum(Table[Points])))

[Anonymous]

@amitchandak sorry doesn't work..