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