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