Forum Discussion
Post PatronCount filtered by measure and circular refrence
Hi Pmorg73 ,
Some things that will help you :
1) The attributes in house drill that are duplicated shouldn't belong in the house drill table.
Ex: Floor Area. Floor Area is probably an attribute of Job No and probably belongs in the Job No number
Other attributes that should probably be moved : Budget, Job State, Designer Complexity, Architect ...
2) Your biggest issue is how you handle your Size slicer. You need to change the way you use it. It would beso much easier for you to create a calculated column in the Job No table that have the size.
Right now, what I ended up doing is adding a calculated column in the house drill.
Size = CALCULATE( MIN( 'Size slicer'[Size] ), FILTER( 'Size slicer', 'house drill'[Floor Area] >= 'Size slicer'[Lower] && 'house drill'[Floor Area] < 'Size slicer'[Upper] ) )
3) You probably don't need the two calculated column for Budged per m and Billable per m.I'll send you the pbix I modified in PM
Hi Pmorg73
What is the calculation of your measures [count basic], [Area basic]? And Billed to Billable is >25, this is another measure? What do you mean by relying on the Count? Re write your Count a little bit based on my understanding
Count 1 =
VAR T1=ADDCOLUMNS (
VALUES('house drill'[Job No.]),
"ABCD", [Area basic])
RETURN
COUNTROWS(FILTER(T1,[ABCD]>MIN('Size slicer'[Lower])
&& [ABCD] <=MAX( 'Size slicer'[Upper]))
)
- Pmorg73count basic = distinctCOUNT('house drill'[Job Summary])Area basic = MIN('house drill'[Floor Area])I used Min just to return a single value as the data contains multiple rows per job, all with the same area value returned from our database.
