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 ...
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
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.

    • johnbird's avatar
      johnbird
      Regular Visitor
      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