Forum Discussion

andrea_chiappo's avatar
andrea_chiappo
Helper III
6 years ago
Solved

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:  

UserIdeas = CALCULATE(
                    IF(
                        DISTINCTCOUNT(FactIdeas[ideaID])=0,
                        0,
                        DISTINCTCOUNT(FactIdeas[ideaID])
                    ),
                    FILTER(FactIdeas, FactIdeas[factIdeasTypeID]=1),
                    USERELATIONSHIP(DimUsers[userID], FactIdeas[userID])
                )

 

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

2 Replies