countif function excel replication powerbi dax measure

Question

I need help with replicating the countifs function in Excel to DAX measures in PowerBI:

Filter Cust_Segment, Filter product_Type WHERE Actual_SOL_Wk <= Week Of AND PTS_Wk < WeekOf AND Actual_PTS_Wk > Week Of

+(Plus) Filter Cust_Segment, Filter product_type WHERE Actual_SOL_Wk < = Week Of, PTS_Wk < Week Of AND Where Actual_PTS_Wk IS NULL OR Actual_PTS_Wk is greater than Week of

Can you help me with this?

I know its using countrows and filter function but not entirely sure how to write this Measure in DAX formula?

amitchandak tamerj1 Greg_Deckler lbendlin Jihwan_Kim

greg_deckler · Answer

Anonymous&nbsp;The error you are getting means that somewhere in your formula you are returning a table of values when DAX is expecting a scalar (single value).&nbsp;
&nbsp;
Over the years, I've learned this lesson. It's mostly a waste of time to try to answer complex calculation issues without sample data. It's just too hard to get the DAX correct and the visual configuration correct, etc. This is what is important:
The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.
&nbsp;
Otherwise, it's just not efficient and there are a million variables involved as to why things won't work the way you want.&nbsp;

greg_deckler · Answer

Anonymous&nbsp;Sort of hard to read your function. Sample data is generally better. See this:&nbsp;CO-CU Excel to DAX Translation - Microsoft Power BI Community
Or try this:
Measure = 
  COUNTROWS(
    FILTER('Filter Cust_Segment, Filter product_Type', [Actual_SQL_Wek] &lt;= [Week of] &amp;&amp; [PTS_Wek] &lt; [WeekOf] &amp;&amp; [Actual_PTS_Wk &gt; [Week Of])
  ) 
  +
  COUNTROWS(
      FILTER('Filter Cust_Segment, Filter product_Type', [Actual_SQL_Wek] &lt;= [Week of] &amp;&amp; [PTS_Wek] &lt; [WeekOf] &amp;&amp; ([Actual_PTS_Wk &gt; [Week Of] || [Actual_PTS_Wk = BLANK()))
Otherwise:
Sorry, having trouble following, can you post sample data as text and expected output?Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490The most important parts are:1. Sample data as text, use the table tool in the editing bar2. Expected output from sample data3. Explanation in words of how to get from 1. to 2.

anonymous · Answer

Hello Greg,&nbsp;&nbsp;Can you please re-write this formula exactly: Here the Cust_Segment &amp; product_Type columns are from the SOL_Adherence table and Actual_SOL_Wk, PTS_Wk and Actual PTS_Wk are from PTS_Adherence table. [Week of] is a Measure. Week of is under newly created Measures Table.&nbsp;&nbsp;I previously wrote something like this:&nbsp;PD Not Finished Play 10 = [PD Not Finished Play 4] + [PD Not Finished Play 9] &nbsp;Where:&nbsp;PD Not Finished Play 4 = CALCULATE(COUNTA(SOL_Adherence[Customer_Segment]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('SOL_Adherence',SOL_Adherence[Actual_SOL_Wk].[Date]&lt;=[Week Of]), FILTER('PTS_Adherence',PTS_Adherence[PTS_Wk].[Date]&lt;[Week Of]),FILTER('PTS_Adherence','PTS_Adherence'[Actual_PTS_Wk].[Date]&gt;[Week Of])) &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;PD Not Finished Play 9 = CALCULATE(COUNTA(SOL_Adherence[Customer_Segment]), &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER('SOL_Adherence',SOL_Adherence[Actual_SOL_Wk].[Date]&lt;=[Week Of]), FILTER('PTS_Adherence',PTS_Adherence[PTS_Wk].[Date]&lt;[Week Of]),FILTER('PTS_Adherence','PTS_Adherence'[Actual_PTS_Wk].[Date]&gt;[Week Of]) || FILTER(PTS_Adherence, 'PTS_Adherence'[Actual_PTS_Wk] = "Null")) &nbsp; &nbsp;&nbsp;PD Not Finished Play 4 is showing errors like this:&nbsp;MdxScript(Model) (28,32) Calculation error in measure 'Measure Table'[Day of] : A table of multiple valueswas supplied where a single value was expected.&nbsp;&nbsp;[Day of] and [Week of] were defined as below and they belonged to a different table named Distinct Date&nbsp;&nbsp;Distinct Date = DISTINCT(UNION(SELECTCOLUMNS(SOL_Adherence,"Job_SOL",SOL_Adherence[Job_SOL]),SELECTCOLUMNS(SOL_Velocity,"Act_SOL_Date",SOL_Velocity[Act_SOL_Date])))&nbsp;Week Of = [Day Of] - WEEKDAY([Day Of],2)+1&nbsp;Under [Day of], Distinct Date formula appeared.&nbsp;&nbsp;For PD Not Finished Play 9 is showing errors like this:&nbsp;&nbsp;" A function 'Filter' has been used in a True/False expression that is used as a table filter expression. This is&nbsp;not allowed"&nbsp;&nbsp;Can you please suggest me original queries to fix this problem or correct my existing queries?&nbsp;Greg_Deckler&nbsp;&nbsp;

anonymous · Answer

Hello,&nbsp;&nbsp;Can you at least help me understand the errors?&nbsp;PD Not Finished Play 4 is showing errors like this:&nbsp;MdxScript(Model) (28,32) Calculation error in measure 'Measure Table'[Day of] : A table of multiple valueswas supplied where a single value was expected.&nbsp;&nbsp;For PD Not Finished Play 9 is showing errors like this:&nbsp;&nbsp;" A function 'Filter' has been used in a True/False expression that is used as a table filter expression. This is&nbsp;not allowed"&nbsp;&nbsp;Greg_Deckler&nbsp;Amit&nbsp;amitchandak&nbsp;lbendlin&nbsp;DataInsights&nbsp;speedramps&nbsp; johnt75&nbsp;davehus&nbsp;Ashish_Mathur&nbsp;&nbsp;

anonymous · Answer

Trial 7 = CALCULATE(CALCULATE(COUNTA(SOL_Adherence[product_type]),COUNTA(SOL_Adherence[Customer_Segment])),SOL_Adherence[Actual_SOL_Wk] <= SELECTEDVALUE('Distinct Date'[Week Of].[Date]),PTS_Adherence[PTS_Wk].[Date]< SELECTEDVALUE('Distinct Date'[Week Of].[Date]),PTS_Adherence[Actual_PTS_Wk].[Date]>SELECTEDVALUE('Distinct Date'[Week Of].[Date]))

Hello, I am having one last error in the query above: It is saying:

Error: The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

Can you please help me fix this error? My main issue is how do I count both the Customer Segment & Product Type together using the condition outlined above. I can only count Customer Segment or Product Type but DAX formula does not let me count both. Why is this?

Forum Discussion

countif function excel replication powerbi dax measure

7 Replies

Related content

COUNTIFS Functionality in DAX

Replicate countif excel function in PowerQuery

DAX Function for COUNTIF and/or CALCULATE

How to create countifs in DAX - replicate Excel countifs

Data replication from AKS