Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Allav
Frequent Visitor

Finding equivalent to countifs in power bi

Hello Community!

 

you have helped me before, I am confident you can help me again Man Very Happy

 

I need to replicate the formula used in the screen shot from excel into power bi. to get 1/0 if a milestone was completed based on Case ID_Milstone key (many rows becuase each row is an activity and there could be many activities to 1 milestone) and based on the 'actual end date' (if reported then it means the milestone is completed)I tried to use:

Milestone Completed = CALCULATE(MIN('case data'[Case ID_Milestone])|ALLEXCEPT('case data'|'case data'[Case ID_Milestone]|IF(ISBLANK('case data'[Actual End Date])|0|1)))

but I get an error: "The ALLEXCEPT function expects a table reference expression for argument '3', but a string or numeric expression was used."which I don't fully understand...

 

image.png

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Allav

A simple test here

Column = CALCULATE(COUNT(Sheet1[Actual End Date]),ALLEXCEPT(Sheet1,Sheet1[id-Milestone],Sheet1[Actual End Date_1]))

Column 2 = CALCULATE(COUNTA(Sheet1[id-Milestone]),ALLEXCEPT(Sheet1,Sheet1[id-Milestone],Sheet1[Actual End Date_1]))

Column 3 = IF([Column]=[Column 2],1,0)

11.png

 

Best Regards

Maggie

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Allav

A simple test here

Column = CALCULATE(COUNT(Sheet1[Actual End Date]),ALLEXCEPT(Sheet1,Sheet1[id-Milestone],Sheet1[Actual End Date_1]))

Column 2 = CALCULATE(COUNTA(Sheet1[id-Milestone]),ALLEXCEPT(Sheet1,Sheet1[id-Milestone],Sheet1[Actual End Date_1]))

Column 3 = IF([Column]=[Column 2],1,0)

11.png

 

Best Regards

Maggie

v-juanli-msft
Community Support
Community Support

Hi @Allav

First let me confirm with your requirement.

For each id of different milestones, if a "Actual End Date" shows, it means this milestone is completed.

But it seems my understanding above doesn't meet the results in your screenshot in the last column.

 

Please check the reference below:

ALLEXCEPT Function (DAX)

CALCULATE Function (DAX)

FILTER Function (DAX)

 

Best regards

maggie

Hi @v-juanli-msft,

each milestone has multiple rows in the table (since there are few activities accosiated to the milestone), so only if "actual end date" is shown for all the activities under the same milestone, the milestone would be completed. see for example, Case_ID_Milestone 1004_M1, there are 3 lines for it in the excel, only 2 have "actual end date" reported, hence in the "milestone completed" it is marked with 0, meaning milestone is not completed.

is it more clear?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.