Countifs function for Power BI query for mulitple columns

[EricKautz]

I am trying to build a report that mimics an excel file. I am trying to get a count for muliple columns meeting multiple criteria. Below is the excel formula that is being used.

=COUNTIFS('Month3 - DataMart'!B:B, "WSS", 'Month3 - DataMart'!DA:DA, "<>Internal") + COUNTIFS('Month3 - DataMart'!DA:DA, "Internal", 'Month3 - DataMart'!DC:DC, "<>OTHER")

These are the column headers in Power BI

B:B = Primary_Asset_Class

DA:DA = Int_Ext_Indicator

DC:DC = Party_Type

Any help is appreciated

[NaveenGandhi · Accepted solution]

HI @EricKautz 

Below DAX should work.

CountResult =
CALCULATE(
COUNTROWS('Month3 - DataMart'),
'Month3 - DataMart'[Primary_Asset_Class] = "WSS",
'Month3 - DataMart'[Int_Ext_Indicator] <> "Internal"
) +
CALCULATE(
COUNTROWS('Month3 - DataMart'),
'Month3 - DataMart'[Int_Ext_Indicator] = "Internal",
'Month3 - DataMart'[Party_Type] <> "OTHER"
)

Let me know if this helps.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!

[EricKautz]

Let me add another layer I forgot. the data from each piece come from different tables fed into power bi. Report breakdown below.

Primary_Asset_Class from CM_Product

Int_Ext_Indicator from CM_Customer

Party_Type from CM_Customer

[NaveenGandhi]

@EricKautz 

What is the relationship between CM_Product and CM_Customer and can you share some screenshot of your expected output?

regards,
NG

[EricKautz]

For the Power BI Report I'm trying to create, when it pulls in all the data via and ODBC connection, their are 2 distinct data tables the data is being pulled from. One data table is CM_Product and the other data table is CM_Customer. Both tables are completely independent of one another, but each table has a column that matches each other so it allows me to link these 2 data tables together.

[NaveenGandhi]

@EricKautz 

If you have linked those two tables up, The DAX i provided earlier should work. If you have any specific issue with that, Please provide screenshot and sample output that you are expectng to understanding the problem better.

Thanks,

NG