The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi everyone! Hoping some can help me with a DAX formula for a measure in Power BI.
In this scenario, lines are made up of segments. Lines can contain significant segments. I need a formula that checks if a line contains at least one significant segment and if it does, return the sum of the lengths of the lines that contain significant segments.
So in the scenario below, it would be the sum of the lengths of lines 1, 2 and 4:
Total Line feet = 10 + 65 + 30
Total Line feet = 105
Any help would be greatly appreciated!
Line Name | Segment Length (ft) | Significant | Line Length (ft) (sum of segments) |
1 | 2 | Yes | 10 |
1 | 2 | No | 10 |
1 | 5 | No | 10 |
1 | 1 | No | 10 |
2 | 10 | Yes | 65 |
2 | 15 | Yes | 65 |
2 | 20 | Yes | 65 |
2 | 20 | Yes | 65 |
3 | 3 | No | 7 |
3 | 4 | No | 7 |
4 | 9 | Yes | 30 |
4 | 14 | No | 30 |
4 | 7 | Yes | 30 |
Solved! Go to Solution.
hi @apelleti
try to
1)create a measure with this:
Hi @apelleti ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create 3 measures.
Measure =
MAXX(FILTER(ALL('Table'),'Table'[Line Name]=SELECTEDVALUE('Table'[Line Name])),'Table'[Significant])
Measure 2 = sUMX(FILTER(ALL('Table'),'Table'[Line Name]=SELECTEDVALUE('Table'[Line Name])),'Table'[Segment Length (ft)])
SignificantLen =
var _1=SUMX(FILTER(ALL('Table'),'Table'[Line Name]=SELECTEDVALUE('Table'[Line Name])),'Table'[Segment Length (ft)])
var _2=SUMMARIZE('Table','Table'[Line Name],"aaa",[Measure 2])
var _3=CALCULATE(SUM('Table'[Segment Length (ft)]),FILTER(ALL('Table'),[Measure]="No"))
return
IF(HASONEVALUE('Table'[Line Name])&&[Measure]="Yes",[Measure 2],IF([Measure]="No",BLANK(),SUMX(_2,[aaa])-_3))
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your reply! I can't seem to get the right numbers. What formula did you use to calculate the line length from the segment lengths?
@apelleti I thought line length is native from your sample data. but it can be calculated.
I created it using:
hi @apelleti
try to
1)create a measure with this: