count number of filtered rows

[Redex]

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,

[Redex · Accepted solution]

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.

[BhaveshPatel]

You do need any expression to create this.

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

[Redex]

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 . 

[BhaveshPatel]

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

[Redex]

@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 

[BhaveshPatel]

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

Use col2 values as slicers.