Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

countif function excel replication powerbi dax measure

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_...
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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?
 
 
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

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