topic Re: Column that replaces a measure in DAX Commands and Tips

Column that replaces a measure

Chris2016 — Wed, 06 Sep 2023 18:09:47 GMT

Hello,

I have a table that looks at exams statuses by users:

User	Test	Passed
A	Z	1
A	X
A	Y	1
A	W	1
B	Y	1
B	W
B	Z	1
C	Z	1
C	W
C	X	1
D	W
D	Z	1
E	Y	1
E	W	1
F	X	1
F	Z

I have a measure that calculates % Passed for each user:

% Passed = DIVIDE(SUM([Passed]), COUNT('Table'[Test]), 0)

I need to tag each user as having "bad results" (with a % Passed < 60%) or "good results" (with a % Passed >= 60%).

I already have a working solution where I am creating a new table with SUMMARIZE to get the aggregate results for users:

Table:

Results =
SUMMARIZE (
    FILTER ( 'Table','Table'[User] <> BLANK () ),
  'Table'[User],
    "Result", [% Passed])

Column:

Results = IF([Result]>=0.6, "Good", "Bad")

But I would like to be able to create a column directly in my table and not use a calculated table.

Any ideas how I can do that?

Thanks a lot!

Re: Column that replaces a measure

some_bih — Wed, 06 Sep 2023 20:25:53 GMT

Hi Chris2016 possible solution "in table": please create 4 calculated column (or less if you want to combine some steps), ajdust Sheet1 to your Table name.

Did I answer correctly? Kudos appreciate / accept solution.

Sum per User =

CALCULATE(

SUM(Sheet1[Passed]),

ALLEXCEPT(Sheet1,Sheet1[User])

)

Count per User =

CALCULATE(

COUNT(Sheet1[User]),

ALLEXCEPT(Sheet1,Sheet1[User])

)

% Result = DIVIDE(Sheet1[Sum per User],Sheet1[Count per User])

Results = IF([% Result]<0.6,"Bad", "Good")

Output

Overview

Re: Column that replaces a measure

Chris2016 — Thu, 07 Sep 2023 07:56:49 GMT

Thanks,
I put the it all in a single column:

Result =
var SumPerUser =
CALCULATE(
    SUM(MyTable[Passed]),
    ALLEXCEPT(MyTable,MyTable[User])
        )
var CountPerUser =
CALCULATE(
    COUNT(MyTable[User]),
    ALLEXCEPT(MyTable,MyTable[User])
        )
var perc=  DIVIDE(SumPerUser,CountPerUser,0)
return if (perc < 0.6, "Bad", "Good")

Many thanks for your help!