Forum Discussion

jjohnson1990's avatar
jjohnson1990
Frequent Visitor
6 years ago
Solved

Sum for filtered in distinct value

HELLO ALL, I currently have a table with below for columns. Sec SECTORDATE DEP DLY MIN PAX DEP DELAY CODE 5 1/1/20 0:00 20 167 15 - PH 8 1/1/20 0:00 85 167 91 - RL 10 1/1...
Need Help
  • jjohnson1990's avatar
    jjohnson1990
    6 years ago

    Hello az38 , thanks for the revert. i had run your code, it give me a constant value regardless of date 

    On modifying the code slightly i.e. removing the filter expression ALL(Query), this gave me the correct output as expected

     

    Total PAX = CALCULATE(SUMX(SUMMARIZE('Query1','Query1'[Sec], "Total PAX", MAX(Query1[PAX])), [Total PAX]))

     

     

     

     

jjohnson1990's avatar
jjohnson1990
Frequent Visitor
6 years ago

hello aj1973,

the above table is only a synopsis of the table data. the duplicate data comes due to a left join condition in sql which cannot be avoided due to data beign pulled in from other tables.

aj1973's avatar
aj1973
Icon for Community Champion rankCommunity Champion
6 years ago

jjohnson1990 

 

doesn't matter how it is linked in the source SQL. If you are willing to remove the duplicate then use Power query in query editor to clean and transform your data. Remember, for performance purposes you need to clean, get rid of none necessary data and transform data in Query Editor to get the most in your visuals and the use of DAX in the Power BI desktop.

 

Regards