Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I know this issue has been raised before in the forum, but still I cannot get it to work.
I've got the most simple example:
3 tables: products, orders and calendar.
products have a status
there are 2 measures: orders this year, orders last year.
When filtering on status in the slicer, this should only be done for orders this year.
We want to see all orders of last year, regardless of the product status. That simple.
Yet when I use the dax
Ordered last year = CALCULATE(sum(Orders[Ordered qty.])
;'Calender'[YearsAgo]=1
;ALL(Products[Status]))
It does not display the products sold last year.
Actually, in my real pbix (I'm using an example here) it's not even showing a total for the 'last year' measure.
Hope anyone has a suggestion.
Thanks,
Johan.
Solved! Go to Solution.
Hi @Johan,
Create a table with status:
Status
Open |
Planned |
Production |
Then make an inactive relationship between Status table and Products Table:
Change your two measure to the following code:
Ordered last year = CALCULATE ( SUM ( Orders[Ordered qty.] ); 'Calendar'[YearsAgo] = 1 ) Ordered this year = CALCULATE ( SUM ( Orders[Ordered qty.] ); 'Calendar'[YearsAgo] = 0; USERELATIONSHIP ( 'Status'[Status]; Products[Status] ) )
Now use the Status table on your slicer and everything should work as you want:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Johan,
Believe that your problem is not DAX related, but visual related, you need to select the Ediut interactions and remove the slicer interaction from the table with order from last year.
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks for taking the time to help.
Edit interactions can help is some situations, but not all.
What if I want to display them in 1 table?
Slicer filter = Planned (should only apply to this year)
This year | Last Year | ||
Mercedes | Planned | 20 | |
Ford | Planned | 25 | |
Audi | Closed | 30 | |
Toyota | Closed | 35 | |
Ferrari | Closed | 40 | |
Lada | Closed | 45 |
Kind regards,
Johan
Hi @Johan,
Create a table with status:
Status
Open |
Planned |
Production |
Then make an inactive relationship between Status table and Products Table:
Change your two measure to the following code:
Ordered last year = CALCULATE ( SUM ( Orders[Ordered qty.] ); 'Calendar'[YearsAgo] = 1 ) Ordered this year = CALCULATE ( SUM ( Orders[Ordered qty.] ); 'Calendar'[YearsAgo] = 0; USERELATIONSHIP ( 'Status'[Status]; Products[Status] ) )
Now use the Status table on your slicer and everything should work as you want:
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks a lot MFelix, this really helped.
I don't understand quite yet why the relation must be inactive in order to have the all() condition working. But it's working, thanks again!
Johan.
Hi @Johan,
The UUSERRELATIONSHIP is not making the ALL to work. When you do measure these are based on context so if you have on your visuals year / descriptions whatever type of aggregators the measures will work accoding to those context, as you put in your first post the use of ALL will take the filters out and surpass the context of the visual, however you are placing in your visual 2 conflicting measures.
When you place the status of the project and on the table and make it interact with the slicer but then you add a measure that gets all the results the slicer will not be overwirtten since the values come from the same table so you are getting ALL the values from a subset of values and not from the full data.
Creating a separeted table with status you are able to make that the original data is presented and applying the slicer only to the related information in this case, the PY is not related with status (that the inactive part of the relationship) and the current year is activated from the slicer that uses USERRELATIONSHIP to activate the relatioship and use context.
You are using the new table only for slicing mode not for making part of the table that's so the interaction between the two do not overwrites for PY.
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |