- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Column used in measure and Used in table as well
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Did I answer your question? Mark my post as a solution! Kudos are also appreciated!
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypa HoosierBI on YouTube
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
08-13-2024 04:26 AM | |||
01-27-2024 02:12 AM | |||
05-29-2024 01:59 PM | |||
10-02-2023 03:23 PM | |||
03-11-2024 09:25 AM |
User | Count |
---|---|
21 | |
17 | |
12 | |
6 | |
5 |
User | Count |
---|---|
29 | |
25 | |
20 | |
13 | |
10 |