Forum Discussion
Tabular view decontestualizes calculation
I want to calculate the number of ideas proposed by each user and the number of projects each user is involved in.
Below a snapshot of the three dimensions involved
where the inactive relationships are:
DimUsers[userID] <---> FactIdeas[userID] and DimUsers[userID] <---> FactProjects[userID]
I took care of checking that there is no active relationship joining the three in a circular loop.
I was able to calculate the first desired quantity adding a calculated column in DimUsers, with the following:
where the FILTER only serve to calculate the right numbers
For the second desired quantity, I tried repeating the above: adding a calculated column,
replacing the Ideas with Projects, plus changing the condition in Filter,
but it gave the (annoying) circular dependency error.
I checked on other pages of this community, and apparently its a limitation of Power BI
(a shortcoming which should be remedied, IMHO) and instead a measure should be calculated.
I then tried displaying the results in a table, containing the user name, role, number of ideas and number of projects.
The problem with using a measure in a table is that it apparently decontestualises the calculation,
cancelling the action of the filters.
Below, the table with only the first three entries (rightmost two columns coming from the query,
the thrid from the calculated column with the formula above)
This all looks fine
However, when I add the measure, the table starts displaying users from other customers.
See, from the last row with values on the Idee column, it´s all data from other customers.
I select the desidered costumer via a slicer on a DimCustomers.
I checked, the measure correctly displays the total number of projects on a Card,
but on the table I have this decontestualisation issue.
Can anybody suggest why? Thanks a lot
Would need to be able to recreate this to determine what is going on. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
2 Replies
- Greg_DecklerCommunity Champion
Would need to be able to recreate this to determine what is going on. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
- andrea_chiappoHelper III
There's no solution and creating a MWE, with sensitive information removed, would take too long.
Thanks anyway for looking into this