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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Count Row if the result of the measure is greater than zero

Hi,

Can you help me write a DAX command that will count the raw that satisfy this condition.

I have a table of clients and dates of recent activity.

The "6 months" column check how much activity has been in the last 6 months in specific types of tasks.

The "12 months" column works in a similar way.

LastDate = MAXX(
FILTER(Absences,Absences[Type]=113 ||
Absences[Type]=87 ||
Absences[Type]=273 ||
Absences[Type]=276 ||
Absences[Type]=274 ||
Absences[Type]=116 ||
Absences[Type]=263 ||
Absences[Type]=266 ||
Absences[Type]=232
),Absences[syscreated])

6 month = IF([LastDate]<TODAY()-182,0,1)

12 month = IF([LastDate]<TODAY()-365,0,1)

How to count green items in both columns? (how many companies have had tasks in the last 6 or 12 months?)

1 ACCEPTED SOLUTION
Super User

The code works fine as it was but you need the filters that you are applyting to the table visual (ClassificationID and TextFiled14) applied at the page level instead. Otherwise you'll get different results in the table and elsewhere, because the filters are different. You can in any case simplify the measure above a bit:

``````Green 6 or 12 =
COUNTROWS (
FILTER (
DISTINCT ( cicmpy[cmp_code] ),
( [6 miesięcy_miara] + [12 miesięcy_miara] ) > 0
)
)
``````

Notice that the result is exactly the same as checking for green on 12 months only, since checking if a date is within the last 12 months is equivalent to checking if a date is within the last 6 months OR within the last 12 months

See it all at work in the attached file (Page 2)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

5 REPLIES 5
Super User

The code works fine as it was but you need the filters that you are applyting to the table visual (ClassificationID and TextFiled14) applied at the page level instead. Otherwise you'll get different results in the table and elsewhere, because the filters are different. You can in any case simplify the measure above a bit:

``````Green 6 or 12 =
COUNTROWS (
FILTER (
DISTINCT ( cicmpy[cmp_code] ),
( [6 miesięcy_miara] + [12 miesięcy_miara] ) > 0
)
)
``````

Notice that the result is exactly the same as checking for green on 12 months only, since checking if a date is within the last 12 months is equivalent to checking if a date is within the last 6 months OR within the last 12 months

See it all at work in the attached file (Page 2)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Super User

Hi @amiskow

How do you want the result?

Place this measure in a card visual:

``````Measure =
COUNTROWS (
FILTER (
ADDCOLUMNS ( DISTINCT ( Table1[Company] ), "@Lasdate", [LastDate] ),
([6 months] + [12 month]) > 0
)
)
``````

If this doesn't work please share a pbix with dummy data reproducing the issue

By the way, you can simplify the code for [LastDate] using the IN operator

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Helper I

Hi @AlB ,

Thank you for contacting me. Your solution does not work correctly, or I cannot make it. 😞

I'm sending the file http://gofile.me/4vwta/cXXTFhjYm

Community Champion

Hi @amiskow ,

Can you please a bit a clearer about your requirement? Are you looking to get the count of the green values from 6 months and 12 months columns?

If yes, what is thelogic behind the red and green colurs in your table?

Helper I

Hi @PC2790 ,

I want to know how many green rows is in the table (single rows), not the sum of the numbers in the cells .

In the photo we have 2 green rows in the 6 month column, and 3 rows in the 12 month column.

The table actually over 10k rows.

The logic is as described above.

"Last date" is a measure that searches for the latest  task from SQL table a given task type, it is not very important for this case.

"6 month" is measure with simple logic that allows conditional formatting. (6 month = IF([LastDate]<TODAY()-182,0,1))

"12 month" is measure same as 6 only 12

I hope I have described the cave enough 😉

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors