Forum Discussion
Dividing Measure Calculation by Column from another table
Hi walkery,
If you are using the Direct Query you cannot create new tables directly check the Using DirectQuery in PowerBI link that as the complete details and limitations.
Regarding your second question there is a relationship between both dimension tables and the main tables, then I use the dimension tables on the visuals to create context.
Regards,
MFelix
Hey walkery
Variables are really helpful in situations like these. Try something like:
NewMeasure =
VAR CurrentMonth = SELECTEDVALUE('Table A'[Month])
VAR CurrentOffice = SELECTEDVALUE('Table A'[Office])
VAR CurrentOfficeCommission =
CALCULATE(
SUM('Table A'[OfficeCommission]),
FILTER(
ALLSELECTED(Table A),
'Table A'[Office] = CurrentOffice && 'Table A'[Month] = CurrentMonth
)
)
VAR CurrentOfficeRevenue =
CALCULATE(
SUM('Table B'[OfficeRevenue]),
FILTER(
ALLSELECTED(Table B),
'Table B'[Office] = CurrentOffice && 'Table B'[Month] = CurrentMonth
)
)
RETURN
DIVIDE(CurrentOfficeCommission,CurrentOfficeRevenue)
Hope this helps,
Parker
Hi Anonymous thanks for the quick reply! I tried your solution, but got an error "Function 'Filter' is not supported in this context in DirectQuery mode". Is there a workaround for that? Thanks again!
Ah DirectQuery lmitations... Try checking the "Allow unrestricted measures in DirectQuery mode" box in Options.
Thanks Anonymous. That worked with not getting the error but the new measure is still just returning blank values. Do I have to have my data structured in any specifc way to get the measure to return actual values?