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

[amiskow]

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?)

[AlB · Accepted solution]

@amiskow 

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 

[AlB]

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 

[amiskow]

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 

[PC2790]

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?

[amiskow]

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 😉