How would I create this table virtually?

[Anonymous]

rather than via maniupulation in power query would it be possible to take the following data:

employee id		gender		score
1		m		3
2		m		7
3		f		5
4		f		8
5		f		4
6		m		6
7		m		9
8		f		2
9		m		1
10		f		4

and turn it into this virtually? (count of those with score >5 (true) and =<5 (false))

gender		> 5		count
m		TRUE		3
m		FALSE		2
f		TRUE		1
f		FALSE		4

ideally i would like adjust the score threshold via a slicer too, presumably this would done using SELECTEDVALUE(slicer) and SWITCH within the DAX for the above?

any help would be greatly appreciated.

[jgeddes]

You can try something like this...

scoreSummarytable = 
var _threshold =
5
var _vTable = 
UNION(
    ROW("gender", "m", "greaterThanThreshold", TRUE(), "score", CALCULATE(COUNT(scoreTable[employee id]), scoreTable[score] > _threshold && scoreTable[gender] = "m")),
    ROW("gender", "m", "greaterThanThreshold", FALSE(), "score", CALCULATE(COUNT(scoreTable[employee id]), scoreTable[score] <= _threshold && scoreTable[gender] = "m")),
    ROW("gender", "f", "greaterThanThreshold", TRUE(), "score", CALCULATE(COUNT(scoreTable[employee id]), scoreTable[score] > _threshold && scoreTable[gender] = "f")),
    ROW("gender", "f", "greaterThanThreshold", FALSE(), "score", CALCULATE(COUNT(scoreTable[employee id]), scoreTable[score] <= _threshold && scoreTable[gender] = "f"))
)
Return
_vTable

[Anonymous]

Oh I'm sorry! When you say virtually you say that you want a dax calculated table? I thought you was saying that does not want to create any table

[Anonymous]

Hi Vitor, Yes, created via DAX, I presume it might involve CALCULATETABLE, ADDCOLUMNS etc but I am struggling with how to create this.

[Anonymous]

Try this out please,

First, if possible, you create this column on the main table: SCORE <5 = if('table'[score] < 5, TRUE, FALSE)

then you try:

#edit

SUMMARIZE(
'table',
'table'[gender],
'table'[SCORE <5],
"Count", countrows('table')
)

[Anonymous]

Hello @Anonymous,

If i had this same problem to resolve, I would make like this:

Put the "Gender" column on a table visual, then create a measure: [[  Score <5 = if('score'[score] < 5, TRUE, FALSE)  ]] and put in the same table.

Then put the "Employee id" go on the field and choose "Count".

😄

[Anonymous]

Hi Vitor,

For the avoidance of doubt, I'd like to create this virtually as I intend on doing doing further manipulation after this step.