cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dilumd Established Member
Established Member

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

1.JPGWithout any filter2.JPGwhen i click on category2 "Quantity_M" doesn't change (which is what i want)3.JPGwhen i apply a filter on dates4.JPGafter 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

Accepted Solutions
Super User
Super User

Re: DAX help

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

10 REPLIES 10
Highlighted
Thim Member
Member

Re: DAX help

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 Established Member
Established Member

Re: DAX help

Hi @Thim

 

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

Super User
Super User

Re: DAX help

@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

 

Try my new Power BI game Cross the River
dilumd Established Member
Established Member

Re: DAX help

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.

dilumd Established Member
Established Member

Re: DAX help

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.

Super User
Super User

Re: DAX help

@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

Try my new Power BI game Cross the River
Super User
Super User

Re: DAX help

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
dilumd Established Member
Established Member

Re: DAX help

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,

5.JPGwhen 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

 

Super User
Super User

Re: DAX help

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

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 271 members 2,994 guests
Please welcome our newest community members: