countif function excel replication powerbi dax measure

[shreep1]

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 

[shreep1]

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?

[shreep1]

@Greg_Deckler @amitchandak  can you please help me resolve this error?

[shreep1]

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

I used the && operator to fix this issue, but instead of returning a number like 4500 its returning True in the 123 card. 

[Greg_Deckler]

@shreep1 Sort of hard to read your function. Sample data is generally better. See this: 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] <= [Week of] && [PTS_Wek] < [WeekOf] && [Actual_PTS_Wk > [Week Of])
  ) 
  +
  COUNTROWS(
      FILTER('Filter Cust_Segment, Filter product_Type', [Actual_SQL_Wek] <= [Week of] && [PTS_Wek] < [WeekOf] && ([Actual_PTS_Wk > [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/1116882

Also, 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/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[shreep1]

Hello Greg, 

Can you please re-write this formula exactly: Here the Cust_Segment & 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. 

I previously wrote something like this:

PD Not Finished Play 10 = [PD Not Finished Play 4] + [PD Not Finished Play 9]

Where:

PD Not Finished Play 4 = CALCULATE(COUNTA(SOL_Adherence[Customer_Segment]),                                                     FILTER('SOL_Adherence',SOL_Adherence[Actual_SOL_Wk].[Date]<=[Week Of]), FILTER('PTS_Adherence',PTS_Adherence[PTS_Wk].[Date]<[Week Of]),FILTER('PTS_Adherence','PTS_Adherence'[Actual_PTS_Wk].[Date]>[Week Of]))        

PD Not Finished Play 9 = CALCULATE(COUNTA(SOL_Adherence[Customer_Segment]),                                                     FILTER('SOL_Adherence',SOL_Adherence[Actual_SOL_Wk].[Date]<=[Week Of]), FILTER('PTS_Adherence',PTS_Adherence[PTS_Wk].[Date]<[Week Of]),FILTER('PTS_Adherence','PTS_Adherence'[Actual_PTS_Wk].[Date]>[Week Of]) || FILTER(PTS_Adherence, 'PTS_Adherence'[Actual_PTS_Wk] = "Null"))  

PD Not Finished Play 4 is showing errors like this:

MdxScript(Model) (28,32) Calculation error in measure 'Measure Table'[Day of] : A table of multiple values

was supplied where a single value was expected. 

[Day of] and [Week of] were defined as below and they belonged to a different table named Distinct Date 

Distinct Date = DISTINCT(UNION(SELECTCOLUMNS(SOL_Adherence,"Job_SOL",SOL_Adherence[Job_SOL]),SELECTCOLUMNS(SOL_Velocity,"Act_SOL_Date",SOL_Velocity[Act_SOL_Date])))

Week Of = [Day Of] - WEEKDAY([Day Of],2)+1

Under [Day of], Distinct Date formula appeared. 

For PD Not Finished Play 9 is showing errors like this: 

" A function 'Filter' has been used in a True/False expression that is used as a table filter expression. This is 

not allowed" 

Can you please suggest me original queries to fix this problem or correct my existing queries?

@Greg_Deckler 

[shreep1]

Hello, 

Can you at least help me understand the errors?

PD Not Finished Play 4 is showing errors like this:

MdxScript(Model) (28,32) Calculation error in measure 'Measure Table'[Day of] : A table of multiple values

was supplied where a single value was expected. 

For PD Not Finished Play 9 is showing errors like this: 

" A function 'Filter' has been used in a True/False expression that is used as a table filter expression. This is 

not allowed" 

@Greg_Deckler @Amit @amitchandak @lbendlin @DataInsights @speedramps  @johnt75 @davehus @Ashish_Mathur 

[Greg_Deckler]

@shreep1 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). 

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 bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Otherwise, it's just not efficient and there are a million variables involved as to why things won't work the way you want.