topic Re: Column used in measure and Used in table as well in DAX Commands and Tips

Column used in measure and Used in table as well

Anonymous — Thu, 07 May 2020 17:44:25 GMT

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

Re: Column used in measure and Used in table as well

mahoneypat — Fri, 08 May 2020 01:44:00 GMT

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

Re: Column used in measure and Used in table as well

Anonymous — Sun, 10 May 2020 13:02:51 GMT

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

Re: Column used in measure and Used in table as well

mahoneypat — Sun, 10 May 2020 17:33:04 GMT

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

Re: Column used in measure and Used in table as well

Anonymous — Sun, 10 May 2020 19:39:41 GMT

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