Forum Discussion
Countifs function for Power BI query for mulitple columns
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
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!!!
5 Replies
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!!!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
EricKautz
What is the relationship between CM_Product and CM_Customer and can you share some screenshot of your expected output?
regards,
NG