Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi, I need help on DAX while calculating the average of Ecode. Condition would be if count of S code is matching with the distinct count of E code it will exclude while calculating average. If count of S code is not matching with the distinct count of E code then it will take distinct count of E code and based on that I need to calculate average. What should be the approach. Any help would be really appreciated.Shown here how it would be calculated
Solved! Go to Solution.
Is this what you are looking for?
Control = VAR _DisE =DISTINCTCOUNT('Table'[E Code])
VAR _CounS = COUNT('Table'[S Code])
VAR _Cont = IF(_DisE<> _CounS,_DisE)
RETURN
_Cont
Flag = IF([Control],1,0)
Final = var _t1 = SUMX(VALUES('Table'[S Code]),[Control])
VAR _t2 = SUMX(VALUES('Table'[S Code]),[Flag])
RETURN
_t1/_t2
Sample PBIX file attached
https://dropmefiles.com/EJ14P
Is this what you are looking for?
Control = VAR _DisE =DISTINCTCOUNT('Table'[E Code])
VAR _CounS = COUNT('Table'[S Code])
VAR _Cont = IF(_DisE<> _CounS,_DisE)
RETURN
_Cont
Flag = IF([Control],1,0)
Final = var _t1 = SUMX(VALUES('Table'[S Code]),[Control])
VAR _t2 = SUMX(VALUES('Table'[S Code]),[Flag])
RETURN
_t1/_t2
Sample PBIX file attached
https://dropmefiles.com/EJ14P
Here is one approach to solve the problem in DAX:
Match = COUNT(S_Code) = DISTINCTCOUNT(E_Code)
E_Code_Count = IF([Match]=TRUE(),0,DISTINCTCOUNT(E_Code))
Avg_E_Code = AVERAGEX(FILTER(ALL(Table),[E_Code_Count]>0),[E_Code])
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
64 | |
52 | |
47 |
User | Count |
---|---|
219 | |
89 | |
76 | |
67 | |
60 |