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
dilumd
Solution Supplier
Solution Supplier

DAX help

Hi All,

 

I have below simple table,

DateCategory1Category2Qty
1/1/2018AQ2
1/2/2018AS3
1/3/2018BR4
1/4/2018BT6
1/5/2018CY4
2/1/2018AQ9

 

I want to calculate total quantity column which doesn't change based on the category2 filter. I wrote a function as follows which returns the expected results. But when I apply filter to a date column this behavior doesn’t persist (check below screenshots).

 

Function,

Quantity_M = CALCULATE(SUM(Test_Table[Qty]),ALLEXCEPT(Test_Table,Test_Table[Date],Test_Table[Category1]))

Without any filterWithout any filterwhen i click on category2 "Quantity_M" doesn't change (which is what i want)when i click on category2 "Quantity_M" doesn't change (which is what i want)when i apply a filter on dateswhen i apply a filter on datesafter the date filter when i click on "category2" (above behavior changes and "allexcept" function doesn't apply after that.after the date filter when i click on "category2" (above behavior changes and "allexcept" function doesn't apply after that.

Can some help me to understand this and tell me how to get it right please.

1 ACCEPTED SOLUTION

Hey @dilumd,

 

understand! With your current data model (just one table) this will become a nightmare, for this reason I recommend to adjust your datamodel to this

image.png

 

I set the Cross filter direction between table1 and "Category2", because category2 seems less important to me than category1, maybe I err. But this allows that the content of the slicer for "Category 2" based on the coresponding table, namely "Category 2" reacts to selections of the slicer Category 1
Category1 --> Table1 <--> Category2

 
I created the tables Category 1 and Category 2, using these simple DAX statemens, e.g. Category 1:

Category 1 = 
VALUES(Table1[Category1]) 

Then I created a measure like so:

Quantity_T = 
CALCULATE(
    SUM(Table1[Qty])
    ,ALL('Category 2'[Category2])
)

This results to this report - please be aware that the content of the Category slicers is coming from the category tables:

image.png

 

Hopefully this is what you are looking for, or at least gives an idea, of course the new measure returns 19, if nothing from Category 1 is selected 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

10 REPLIES 10
Thim
Resolver V
Resolver V

I'm not sure why your function dosn't act as expected, but is it important to you, that it is a DAX formula that makes the qty persist the filter?

 

Instead you could change the interaction, with the qty and catagory 2.

 

You can do this by clicking on the slicer "Catagory 2", 

Then choose Format and Edit intereactions.

Then change the intereaction on the qty.

qty.PNG

Then this field will no longer be affected by the choices of the catagory 2 slicer.

dilumd
Solution Supplier
Solution Supplier

Hi @Thim

 

Thanks for your reply! But I need this behavior for my other calculations, edit interactions doesn’t serve the purpose for me.

@dilumd

 

This is how I would expect the results to be

because you are removing the filters from Date and Category1

Your DAX is similar to

 

Quantity_M =
CALCULATE ( SUM ( Test_Table[Qty] ), ALL ( Test_Table[Category2] ) )

You can use ALL(Test_Table) if you want a static result of 28.

The date slicer would filter the dates in the Table but the MEASURE won't change

 


Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

Quantity_M =
CALCULATE ( SUM ( Test_Table[Qty] ), ALL ( Test_Table[Category2] ) )

This function works very similar to my previous all except function. But whenever I add a filter on date that behavior breaks and then it’s getting filtered on category2 as well.

As far as I’m not adding any filter on dates this works but as soon as filter comes on date whole thing breaks and start filtering on category2.

Hope I explain you this now.

 

Thanks a lot.

@dilumd

 

I get your point.

I think the only way is to use Diable interaction between visuals because slicer selection will filter the rows of other visuals irrespective of the MEASURE we write.

 

But may be  i am missing something.

 

Could you help

@TomMartens
@MFelix


Regards
Zubair

Please try my custom visuals

Hey @dilumd,

 

I have to admit that I do not understand what you want to achieve.

 

As no filter is applied your function returns a total of 28

 

This is also true when you apply a filter to categor2, this is because of ALLEXCEPT(...).

 

Now, I'm wondering what you expect if you select something from the slicer category1 in my example I choose A.

This filters down the rows to this

image.png

 

I'm wondering what your function, please let's call it a measure - I assume you used the DAX statement to create a measure - should return.

 

I can imagine these values:

  • 28 this would equal ignore everything
  • 14 the sum of all quantities of category2, Q (2 + 9) and S (3)

What is your expected result if I choose:
category1 = A and

category2 = S

 

Are category1 and category2 in a way independent, meaning something like product and country. And could it be possible that each product could be sold in each country even if this is not reflected by the data.

 

Please provide a simple example what value you expect for some filter settings.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens,

 

Thank you in advance.

 

Answering to your questions,

 

  1. Yes, when I select category1 “A” answer should be 14
  2. Function is a DAX measure
  3. If I select category1 = A and category2 = S answer should still be 14 (as I don't want any kind of filtering on my "category2" column)
  4. we can say category1 and category2 are independent.

Most importantly,

when i filter date From "01st Jan 2018 to 10th Jan 2018"when i filter date From "01st Jan 2018 to 10th Jan 2018"

When I filter date from 01st Jan 2018 to 10th Jan 2018 answer is "19" which is fine, But when I click on cateory2 filter (e.g. "R" in below case) while the date filter is as it is ("01st Jan 2018 to 10th Jan 2018") answer "19" should not be changed, I need Quantity_M as "19" not "4".

 

 6.JPG

 

Hey @dilumd,

 

understand! With your current data model (just one table) this will become a nightmare, for this reason I recommend to adjust your datamodel to this

image.png

 

I set the Cross filter direction between table1 and "Category2", because category2 seems less important to me than category1, maybe I err. But this allows that the content of the slicer for "Category 2" based on the coresponding table, namely "Category 2" reacts to selections of the slicer Category 1
Category1 --> Table1 <--> Category2

 
I created the tables Category 1 and Category 2, using these simple DAX statemens, e.g. Category 1:

Category 1 = 
VALUES(Table1[Category1]) 

Then I created a measure like so:

Quantity_T = 
CALCULATE(
    SUM(Table1[Qty])
    ,ALL('Category 2'[Category2])
)

This results to this report - please be aware that the content of the Category slicers is coming from the category tables:

image.png

 

Hopefully this is what you are looking for, or at least gives an idea, of course the new measure returns 19, if nothing from Category 1 is selected 🙂

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Hi @TomMartens,

 

Thank you! @TomMartens. I have created a separate calendar table and got it done.

 

Hi @Zubair_Muhammad,

 

Thanks for the reply. When i click date and the category1 slicers value should change, but even if i click category2 values should not change.

 

That's my requrement.

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.