Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have the following formula simplified from Excel; I cannot figure out how to replicate this in PowerBI. Any help would be appreciated. All the data is in one table in Power BI. The date qualifications in Excel are sitting in a single cell i.e., A1 = 01/01/2020 ---- FINISH Date < EDATE(A1,1)
=COUNTIFS(
'Sheet 1' FINISH Date >= Jan 2020,
'Sheet 1' FINISH Date < Feb 2020,
'Sheet 1' Actual Finish >= Jan 2020,
'Sheet 1' Actual Finish < Feb 2020,
'Sheet 1' System = "xxx",
'Sheet 1' Type <> "xxxxxxxx")
Thank you
Solved! Go to Solution.
Hi @CaveOfWonders ,
Try this measure.
Measure =
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) &&
Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) &&
Sheet1[System] = "x" && Sheet1[Type] = "y"
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
So in general you replace COUNTIF(S) with a structure like:
COUNTX(FILTER(...),...) or CALCULATE(COUNT(...),FILTER(...))
But, can't really tell what your formula is doing. 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
Thanks Greg,
Hi Greg, thank you for your reply. I'm trying to count, only if:
1 - the date in the Finish Dates Column falls between the 1st and 31st Jan
2 - the date in the Actual Finish Column falls between the 1st and 31st Jan
3 - The System column = "x"
4 - The Type column = "y"
I have tried to create the measure but I'm getting the error (The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.)
Hi @CaveOfWonders ,
Try this measure.
Measure =
CALCULATE(
COUNTROWS(Sheet1),
FILTER(
Sheet1,
Sheet1[Finish date] >= DATE(2020, 1, 1) && Sheet1[Finish date] < DATE(2020, 2, 1) &&
Sheet1[Actual Finish] >= DATE(2020, 1, 1) && Sheet1[Actual Finish] < DATE(2020, 2, 1) &&
Sheet1[System] = "x" && Sheet1[Type] = "y"
)
)
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much Lionel, it worked perfectly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |