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.

Reply
amiskow
Helper I
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.

 

table.png

 

 

 

 

 

 

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
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

@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 

 

SU18_powerbi_badge

AlB
Super User
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 

 

SU18_powerbi_badge

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
Community Champion
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?

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.