Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Adwitiy_4209211
New Member

Questions for optimization and DAX expression

There is one customer column as CUST_CODE which we want to create a calculated column which has the customer value which is filtered based on the REVENUE column. 
Basically we want just those customers which has revenue. So want to create a calculated column. 


I am using this formula and getting the error. 

=CALCULATE(VALUES(FACT_SALES[CUST_CODE]),[Revenue]<>blank())

3 REPLIES 3
tamerj1
Super User
Super User

Hi @Adwitiy_4209211 

the folowing calculated column returns TRUE for the customers that made revenue. 

=
NOT ISBLANK (
    CALCULATE (
        SUM ( ACT_SALES[Revenue] ),
        ALLEXCEPT ( FACT_SALES, FACT_SALES[CUST_CODE] )
    )
)

Thank you @tamerj1 ,
I think I have failed to mention REVENUE is a measure which calculates total Revenue. 
Customer is a dimension column. 
So based on this I want a calculated column of show customers where REVENUE is not blank.  


 

@Adwitiy_4209211 

I understand it is a measure but I guessed it is a sum of a column. However, you can use

=
NOT ISBLANK (
    CALCULATE ( [Revenue], ALLEXCEPT ( FACT_SALES, FACT_SALES[CUST_CODE] ) )
)

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.