Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI ,
i'm working on Condition dax which a bit complicate over here and i have never experienced this before.
We have code , must have SKU etc.. and Count. in this case , BI will count all number and sum together over the different code provide. but i wanna have exceptional over few codes.
Look at yellow highlight on 1317 count 1 , 1108 count 1 , both of them will be 2 as calculation.
but this is exeption : IF 1317 count 1 and 1108 count 1 both of them will count only 1 ,
IF 4295 count 1 and 2239 count 1 both of them will count only 1
For the rest let's calculate as normal sum.
I'll assume you want this as a measure (in a card visual for instance):
Measure = VAR _Sum1 = CALCULATE ( SUM ( Table1[Count] ); NOT Table1[Code] IN { 1317; 1108; 4295; 2239 } ) VAR _Count1317 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 1317 ) VAR _Count1108 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 1108 ) VAR _Count4295 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 4295 ) VAR _Count2239 = LOOKUPVALUE ( Table1[Count]; Table1[Code]; 2239 ) VAR _Sum2 = IF ( _Count1317 = 1 && _Count1108 = 1; 1; CALCULATE ( SUM ( Table1[Count] ); Table1[Code] IN { 1317; 1108 } ) ) VAR _Sum3 = IF ( _Count4295 = 1 && _Count2239 = 1; 1; CALCULATE ( SUM ( Table1[Count] ); Table1[Code] IN { 4295; 2239 } ) ) RETURN _Sum1 + _Sum2 + _Sum3
You probably have to swap the semicolons ";" for commas "," in the code, if you have a US-like locale in your OS.
hi @AlB thx for your effort .
i used above calculation as you mentioned . and when i do testing filter on code number that i put together with Customer Code , it turn out to wrong number , actually the right data is 4295= 1 , 2239= 1 = Total 1
it should not be the number as 8 , 9 or 10 , it's not as expected. because my original data only show count 1 on each customer code with Code , can you please help to correct this? i'm looking forward.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |