ALLEXCEPT and ALL function does not work as I expected in DAX
Hi Everyone, I'm trying to use All/Allexcept functions in some calculations for some case scenarios. I was working with Qlik & QlikSense before and this is easly applicable on Qlik but I could not manage to get this done on PowerBI. This may not be applicable on powerbi or I may not achieve to do it. Eveything explained on the last page(ALL & ALL EXCEPT Examples), but I will give summary here again. (PBIX file : GoogleDriveLink) Page : ALL & ALL EXCEPT Examples What I'm trying to do is, I want to put a few measure on a table and when I make a selection from a field, I want only some calculations get affected from that selection, and some others don't. For example, I will put Master Date as dimension and 3 different calculation on the table. I will make a selection from Service Category field and only 1th. measure will be affected from that selection and others don't. Then I will make another selection from Availability field, then 2.th measure will be affected from the selection etc... I have 4 tables. Appoinment, Marketing, Utilization, Availability. All those 4 tables merged into appointment table. Measure 1: Calculation gets affected from only determined fields in the all except function. Test Appointment Qty = CALCULATE(sum(Appointment[m_appointment_qty]) ,ALLEXCEPT(Appointment ,Appointment[dim_country_id] ,Appointment[dim_country] ,Appointment[dim_master_date] ,Appointment[dim_service_category] ,Appointment[dim_promise_met]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Measure 2: Calculation will not be affected from the determined field selections. Test2 Appointment Qty = CALCULATE( sum(Appointment[m_appointment_qty]) ,ALL(Appointment[data_flag]) ,ALL(Appointment[Availability]) ,ALL(Appointment[dim_day_diff]) ,ALL(Appointment[cleaner_id]) ,Appointment[table_type]="Appointment" ,Appointment[dim_calculation_type]="Original" ) Those calculations works fine if I don't put any dimension into the table but it does not work if I put any dimension. I tried it with and without FILTER function in the calculation but I could not manage this to work with FILTER function. When I put FILTER in dax formula, FILTER function does not allow me to put multiple dimensions to tell the calculation not to affect from any selection. More detailed explanation and use cases are on the page in PBIX file. I'd be so happy if you see the file and help me to write the correct DAX formula. Thanks for your support already. Table 2 Works fine but Table 1 will not be shown if I make any selection. I attached the uses cases as well.
Distinct Count of Triggers
Hi I have below table on PowerBI to demonstrate the High, medium, and low spenders.. DAX used for columns are; HIGH SPEND = IF(AND(OR([1M Rolling SPEND]>=50000,[1D Rolling SPEND]>=20000),[Date]>=DATE(2021,5,17)),"Flag","") HIGH SPEND FLAG count per player = IF(AND('Database'[HIGH SPEND FLAG count per player part 1]=2,COUNTROWS(FILTER('Database',[Number]=EARLIER('Database'[Number]) && 'Database'[HIGH SPEND FLAG count per player part 1]=1))=0),1,'Database'[HIGH SPEND FLAG count per player part 1]) HIGH SPEND FLAG count per player part 1 = IF([HIGH SPEND FLAG]=BLANK(),BLANK(),COUNTROWS(FILTER('Database',[Number]=EARLIER('Database'[Number]) && [HIGH SPEND FLAG]="FLAG" && [Date]<=EARLIER('Database'[Date])))) i am trying to get the results as like on 3rd column HIGH SPEND FLAG Count Per Player(EXPECTED) but above 2 formulas on 1st and 2nd column don't give me results how i want. One of the reason is Employee 1111 was on both locations (X,Y) on 20/04/2022 and has been flagged twice and my current DAX filter doesn't filter it. I am thinking DistinctCount formula might be the solution but but not sure about it. Anyone available for help please? ThanksCreate derived table, remove some filters, add new filters
Hi all, I need some help please I have a table with all the records and types. I need to create a new table from it, as follows: 1) remove activity date filtering (as the records I need do not have a date) 2) keep all other filters (e.g. country) 3) add a new filter (to get only records of type "C" ) I have put it together in a diagram to help explain better: https://i.postimg.cc/cC3YZykz/PBI.png Many thanks for your help!
