Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johan
Advocate II
Advocate II

All() dax not working

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]))

 

AllDaxTest.jpgAllDaxTestData.jpg 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

1 ACCEPTED SOLUTION

Hi @Johan,

 

Create a table with status:

 

Status

Open
Planned

Production

 

Then make an inactive relationship between Status table and Products Table:

Status_relationship.png

 

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:

status.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @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.

 

Sem Título.png

 

Regards

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks 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 yearLast Year
MercedesPlanned20 
FordPlanned25 
AudiClosed 30
ToyotaClosed 35
FerrariClosed 40
LadaClosed 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:

Status_relationship.png

 

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:

status.gif

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.