cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Disitinct count with condition across column

Hello guys,

Please how can i return the results below using a DAX formula?

 

MonthsJanFebMarAprMayJunJul
Street-Number      
NG-PHC-2180868270658040
NG-PHC-3260708890808675
NG-PHC-4580828170828889

Table 1 shows power surge recordings for streets each month.

 

Example: street NG-PHC-21's power situation was critical in the past 3 months and counts as 1 in the results table for the month of March because it has consistently exceeded 80 ohms in its readings for each month(Jan, Feb and March)

 

  Results table   
 JanFebMarAprMayJunJul
NG-PHC-210010000
NG-PHC-320000100
NG-PHC-450010001

Table 2 shows the way i want to represent this information.

 

So street NG-PHC-45, was >80 in Jan, > 80 in Feb, and > 80 in March and so counts as 1 in March in the results table.

again it was >80 for march, < 80 in Apr and >80 in May but this time does not count as 1 in May in the results table.

 

Any help will be appreciated.

5 REPLIES 5
Nathaniel_C
Super User I
Super User I

@Anonymous ,

Or you can create a new table, create a 1 ti 1 relationship between tables, and drop the measure on that table.

 

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

Summary Table = SELECTCOLUMNS(OHMS,"Street",[Column1])

ohms 1.PNG

 

ohms 3.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hello, @nathaniel thanks for your help. Ive tried this but it doesnt work.

 

Ive also tried to get the expected power readings  for each month by creating 3 calculated measures and doing an if statements that looks at the values returned by the calculated measures and returning a 1 for each month where the multiple criterias match and 0 otherwise.

 

MonthsJanFebMarAprMayJunJul
Street-NumberPower RdPower RdPower RdPower RdPower RdPower RdPower Rd
NG-PHC-2180868270658040
NG-PHC-3260708890808675
NG-PHC-4580828170828889

 

Power_over_80 = CALCULATE(COUNT(Table1[Street Number], FILTER(Table1,[Power Rd] >= 80

(This measure returns a "1" for each street each month whose power readings is >or = 80 and  "blank" if otherwise

 

following this I created the 3 measures below 

Current_month = CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd]) >= 80 && Table1[Month] = MONTH(TODAY())))

 

Prev_month = var current_month = MONTH(TODAY()) return CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd] >= 80 && Table1[Month] = current_month - 1))

 

Prev_2month = var current_month = MONTH(TODAY()) return CALCULATE(COUNT(Table1[Street Number]), FILTER(Table1,[Power Rd] >= 80 && Table1[Month] = current_month - 2))

 

and then ran an if statement against the 3 measures

 

Count_last2_months = IF(Table1[Current_month] = 1 && Table1[Prev_month] = 1 && Table1[Prev_2month] = 1, 1,0)

This is the measure i expect should look at the returned value for each month and if it is a "1" for three months in a row it returns "1" on the third month.

 

but i'm still not getting the result i want.

 

Somebody help me!!!!!!

 

Hi @Anonymous ,

What do you mean it does not work?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

 


@Nathaniel_C wrote:

Hi @Anonymous ,

What do you mean it does not work?

@nathaniel i mean it doesnt work the way i want it.


 

Nathaniel_C
Super User I
Super User I

Hi @Anonymous ,

 

Not sure whethear you wanted this as a visual table, but I dropped the street names on the table, and created this measure.

March = IF(max(OHMS[Jan])>=80 && MAX(OHMS[Feb] ) >=80 && MAX(OHMS[Mar]) >= 80, 1,0)

I would note that you say exceeds, but in your summary table you use >= to get the value.

ohms.PNG

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Top Kudoed Authors