topic Re: count number of filtered rows in Desktop

count number of filtered rows

Redex — Mon, 07 Nov 2016 10:15:39 GMT

Hello,

I have a table has 2 columns:

Col1 Col2

A New

A New
B Contacted

C Canceled
C Qualified

I want to summurize these data in new table as follow using dax expression not MATRIX:

Col1 New Contacted Canceled Qualified

A 2 0 0 0
B 0 1 0 0
C 0 0 1 1

what is the expresion will help to do this?

Regards,

Re: count number of filtered rows

BhaveshPatel — Sun, 06 Nov 2016 08:52:46 GMT

You do need any expression to create this.

Use matrix visual to create the expected result. See the attached screenshot.

Re: count number of filtered rows

Redex — Sun, 06 Nov 2016 09:17:47 GMT

Thanks, BhaveshPatel this helped to view the data but cannot interact with them, the reason wanted to use expression because I need to use these values in some equasions .

Re: count number of filtered rows

BhaveshPatel — Sun, 06 Nov 2016 10:55:54 GMT

Can you please post the full question including what other equations you will use these values with.

Re: count number of filtered rows

Redex — Sun, 06 Nov 2016 12:18:17 GMT

BhaveshPatel

my question is simple, regardless about the equasions because its not the issue. simply I need the expresion which could help me to achive the requested.

I need to count the number of filtered rows. example: I have table have 50 rows, and I filtered the rows which contain value "Contacted" . I need to get the number of the filtered rows

Re: count number of filtered rows

BhaveshPatel — Sun, 06 Nov 2016 20:14:00 GMT

filteredRows:=CALCULATE(COUNTA(Table[Col2],ALLSELECTED(Table[col2]))

Use col2 values as slicers.

Re: count number of filtered rows

Redex — Mon, 07 Nov 2016 07:54:58 GMT

Here is the solution:

I created a new table Table 2 which contain the list of Agents name fetched from Table 1 using the following expression:

Table 2 = DISTINCT(Table1[AgentName])

then I created a relationship betwen Table 1 and Table 2 (Many to One *:1) and Cross filter direction (Both)

then, I started creating new columns with folowing expression:

ColumnName1 = CALCULATE ( COUNTROWS (Table 1), Table 1[fieldName] = "Value")

Now I am able to summarize the data as required.