Forum Discussion

johnbird's avatar
johnbird
Regular Visitor
2 years ago
Solved

How to replicated this countifs in PowerBI

Hello, 

I have a table below with a list of ID and dates. A [BindingID] will be repeted for each [ReportingPeriod] if it has been picked in that month. The Binding ID will only apprea once for each month. I want to  replicate the countifs formula I use in Excel in this table in PowerBI "=COUNTIFS($A$2:$A$586,A2,$C$2:$C$586,D2)"

 

So far I have this DAX function but it does not return anything. "

Issue_LastMonth = CALCULATE(COUNTA(dq_issues_tbl[BindingID]),FILTER(dq_issues_tbl,dq_issues_tbl[BindingID] = dq_issues_tbl[BindingID] && FORMAT(dq_issues_tbl[ReportingPeriod],"DD/MM/YYYY") = FORMAT(dq_issues_tbl[LastMonth],"DD/MM/YYYY")))"
 
Is there a way this can be achiveved within PowerBI.

 

BindingIDDQItemIDReportingPeriodLast Month Issue Last Month
684129/02/202431/01/20241
1439629/02/202431/01/20241
19612929/02/202431/01/20241
25716729/02/202431/01/20241
29818829/02/202431/01/20241
43229029/02/202431/01/20241
43429229/02/202431/01/20241
44970929/02/202431/01/20240
45232829/02/202431/01/20240
46131029/02/202431/01/20241
47754829/02/202431/01/20241
47954929/02/202431/01/20241
51232929/02/202431/01/20240
55571029/02/202431/01/20240
68655229/02/202431/01/20241
76033129/02/202431/01/20240
80671429/02/202431/01/20240
80951929/02/202431/01/20241

 

Thanks John

Need Help
  • johnbird's avatar
    johnbird
    2 years ago

    Ok so I decided to ask Microsoft Copilot and it actually worked. This is the solution:

     

    Column =
    VAR BindingID = dq_issues_tbl[BindingID]
    VAR ReportingPeriod = FORMAT(dq_issues_tbl[LastMonth],"DD/MM/YYYY")
    RETURN
        COUNTROWS(
            FILTER(
                ALL(dq_issues_tbl),
                dq_issues_tbl[BindingID] = BindingID &&
                FORMAT(dq_issues_tbl[ReportingPeriod],"DD/MM/YYYY") = ReportingPeriod
            )
        )
     
    Thank for taking a look, Cheers John

4 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Give this DAX measure a try

    Issue_LastMonth = 
CALCULATE(
    COUNTROWS(dq_issues_tbl),
    FILTER(
        dq_issues_tbl,
        dq_issues_tbl[BindingID] = EARLIER(dq_issues_tbl[BindingID]) &&
        FORMAT(dq_issues_tbl[ReportingPeriod], "DD/MM/YYYY") = FORMAT(dq_issues_tbl[Last Month], "DD/MM/YYYY")
    )
)
    • johnbird's avatar
      johnbird
      Regular Visitor
      2 years ago

      Thank you for the quick reply, unfortunatly it returns blank in the column.

      • Greg_Deckler's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        2 years ago

        johnbird Seems like that would be expected behavior. None of your rows have a ReportingPeriod that is equal to the Last Month so it's going to filter out all rows.