Column used in measure and Used in table as well

[Anonymous]

Hello,

I have some trouble with Dax & Power BI

I' using a column as a filter in a Measure : 

Total_Amount = CALCULATE(SUM(Table[amount]);Dimension[Filter_Column]="Primary Cost")

The when I'm adding the measure to the Table Visual, everythink was OK :

 Untill I added the Filter_column to the visual and everything is wrong except the TOTAL of the table visual :

There's any solution for this??

Is this a bug Dax or it's working like that in Power BI?

Thanks a lot for your responses

[Anonymous · Accepted solution]

Hi there.

First, your measure must be written like this:

Total_Amount =
	CALCULATE ( 
		SUM ( Table[amount] ),
		KEEPFILTERS( 
			Dimension[Filter_Column] = "Primary Cost"
		)
	)

Secondly, please NEVER, EVER join 2 fact tables directly to each other. Unless you want to suffer and re-build your model from scratch later on.

Best

D

[mahoneypat]

That is the behavior of DAX.  The filter in the calculate is overriding the filter from the visual if on the same column (hence all the same values).  You could just simplify your measure back to Sum(Table[Amount]) since your visual is doing the filtering now.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hello @mahoneypat

I think the probleme is about the use of the USERELATIONSHIP 

Total_Amount =
CALCULATE (
SUM (Fact2[Amount] );
DATESYTD( 'Date_Table'[Date] );
FILTER ( Dimension; Dimension[Filter_Column] = "Physical");
USERELATIONSHIP ( Fact2[ID]; Fact1[ID] )
)

This is my Schema :

I think when we want to across the dimension column returned from the USERELATIONSHIP then I cant see nothing as result 

I think it's a DAX Logic

[mahoneypat]

I don't think it is the USERELATIONSHIP().  Since you have added the column to your visual that you were filtering on in the measure, have you tried the simpler measure

Total Amount = Sum(Fact2[Amount])

Looking at your model, the filter should propagate from your Dim table to the Fact2 table.  I would discourage bi-di relationships, but this should work in your model.  If you have a 1:1 between Fact1 and Fact2 I would consider merging them in query editor to simplify things.

Regards,

Pat