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.
Hello guys,
Please how can i return the results below using a DAX formula?
Months | Jan | Feb | Mar | Apr | May | Jun | Jul |
Street-Number | |||||||
NG-PHC-21 | 80 | 86 | 82 | 70 | 65 | 80 | 40 |
NG-PHC-32 | 60 | 70 | 88 | 90 | 80 | 86 | 75 |
NG-PHC-45 | 80 | 82 | 81 | 70 | 82 | 88 | 89 |
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 | |||||||
Jan | Feb | Mar | Apr | May | Jun | Jul | |
NG-PHC-21 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
NG-PHC-32 | 0 | 0 | 0 | 0 | 1 | 0 | 0 |
NG-PHC-45 | 0 | 0 | 1 | 0 | 0 | 0 | 1 |
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.
@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])
Proud to be a Super User!
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.
Months | Jan | Feb | Mar | Apr | May | Jun | Jul |
Street-Number | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd | Power Rd |
NG-PHC-21 | 80 | 86 | 82 | 70 | 65 | 80 | 40 |
NG-PHC-32 | 60 | 70 | 88 | 90 | 80 | 86 | 75 |
NG-PHC-45 | 80 | 82 | 81 | 70 | 82 | 88 | 89 |
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?
Proud to be a Super User!
@Nathaniel_C wrote:Hi @Anonymous ,
What do you mean it does not work?
@nathaniel i mean it doesnt work the way i want it.
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.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Proud to be a Super User!
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.