1

I meet some issue when creating a measure on dax My dataset contains data on call center , like this :

enter image description here

The field STAT is "0" for call without answer and "1" for call with answer(picking up) I want to count the number of "0" based on DATE column and ID column (we can have many calls with the same ID on the same DATE) In our case i want the count of these rows : enter image description here

The count will be 4 and i want the count per DATE and ID I want to add a filter on the ID and when i select a specific ID , i want the table to show only one of the 4 rows and the count

This is what i try te = VAR we = CALCULATE( COUNTROWS(DATA), DATA[STAT]="0" )

VAR wt = CALCULATE( COUNTROWS(DATA), DATA[STAT]="1" ) RETURN we - wt

But this formula doesn't take THE ID and DATE in consideration

I think everything is clear can you please help me

Kind regards

Arthur_75
  • 35
  • 2
  • 1
    Re "*But this formula doesn't take THE ID and DATE in consideration*", not sure I understand: that measure will respond to any slicers/filters based on the ID and DATE columns. Suggest you give some specific examples together with expected results. And post copiable data, not pictures. – Jos Woolley Mar 16 '23 at 09:21

1 Answers1

0

Step-1: Create a new Date_new column excluding the itme stamp and contains only DD/MM/YYYY.

Step-2: create this below measure-

count_zero = 
calculate(
    count(your_table_name[STAT],
    filter(
        allexcept(your_table_name, your_table_name[id]),
        //your_table_name[STAT] = 0
        max(your_table_name[STAT]) = 0
    )
)

Step-3: Add a table visual with column/Measure - id, date_new & count_zero.

Step-4: Add the filter pane with id

This should give you the expected output.

mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • thanks for your help but it doesn't match what am looking for Your measure gives me the same output like this formula : we = CALCULATE( COUNTROWS(DATA), DATA[STAT]="0" ) Its only show the number of "0" in the table and i want the number of "0" without a "1" on the same DATE Thanks and King regards – Arthur_75 Mar 16 '23 at 16:03
  • Hi @Arthur_75, can you please try the updated code? – mkRabbani Mar 20 '23 at 04:38