To have Countifs in PowerBI

[Anonymous]

Hi, I am trying to create a table as in sheet2 as below - 

Dump Date	12/17/2021
Status	15-Dec	16-Dec	17-Dec
Tickets Opened per day	2	1	0

with the data coming from base sheet1 as 

Dump Date	Created	Ctype
15-Dec	15-Dec-21	C1
17-Dec	16-Dec-21	C2
16-Dec	14-Dec-21
17-Dec	15-Dec-21	C2
17-Dec	15-Dec-21	C1

If you seee sheet2 the formula being used is like - =COUNTIFS(Sheet1!$A:$A,$D$10,Sheet1!$C:$C,"*",Sheet1!$B:$B,Sheet2!D$12)

I am trying to create the table as in sheet2. please help. thanks,Chandra

[VahidDM]

Hi @Anonymous 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Anonymous]

Hi Vahid,

Sample data table is as below -

Dump Date	Created	Ctype
15-Dec	15-Dec-21	C1
17-Dec	16-Dec-21	C2
16-Dec	14-Dec-21
17-Dec	15-Dec-21	C2
17-Dec	15-Dec-21	C1

Expected output -

Dump Date	12/17/2021
Status	15-Dec	16-Dec	17-Dec
Tickets Opened per day	2	1	0

I have the above table in excel and usign formulae - Dumpdate = today() and for Tickets opened if I click on the row in excel I have formula like =COUNTIFS(Sheet1!$A:$A,$D$10,Sheet1!$C:$C,"*",Sheet1!$B:$B,Sheet2!D$12) ffor getting B4 which is 2 and =COUNTIFS(Sheet1!$A:$A,$B$1,Sheet1!$C:$C,"*",Sheet1!$B:$B,Sheet2!C$3) for getting B5 which  is 1 and =COUNTIFS(Sheet1!$A:$A,$B$1,Sheet1!$C:$C,"*",Sheet1!$B:$B,Sheet2!D$3) for B6 whic is 0

The sheets are related by the formula that I use. Hope this helps, Chandra.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Table1:

Table2:

Dump date = TODAY()

Tickets Opened per day = 
CALCULATE (
    COUNT ( Table1[Ctype] ),
    FILTER (
        Table1,
        [Ctype] <> BLANK ()
            && [Dump date] = EARLIER ( Table2[Dump date] )
            && [Created] = EARLIER ( Table2[Status] )
    )
)

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

I got confused on how the table 2 can even be created? Because I just have the table 1 in my mail as input and the desired output is actually table2. If I create the table 2 or the desired outcome then what are we calculating? Can you please explain? Best Regards, Chandra.

[v-zhangti]

Hi, @Anonymous 

Table 2 can be obtained from Table 1 by using the table manipulation function.

Table 2 = DISTINCT('Table 1'[Dump Date])

Rename the first column to Status again.

Please refer to the attachment if this is the output you are expecting.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.