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.
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!
Solved! Go to 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 ) )
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]
)
),
""
)
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.
Worksheet formula is enough to solve it
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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]
)
),
""
)
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.
@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 ) )
@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])))
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |