Forum Discussion

Junaid11's avatar
Junaid11
Helper V
4 years ago

Dax function recreate

Hello,

I had this data below:

DateSalesOrderRevenueDepartment
11/02/2020A125210P
11/02/2020A256732L
11/02/2020A345627L
12/02/2020A125210P
12/02/2020A271354L
12/02/2020A343362P
12/02/2020A423565P
12/02/2020A45743P

I used below formula to calculate the measure showing New sales order:

Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[Date] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[SalesOrder] )
    ) = 1,
    SUM ( 'Table 1'[Revenue] )
)

It was generating below result which was perfect for me:

it was working fine until I put Department column in the row. The Dax value did not show any value. It was showing it blank but whenever I clicked the department the sales order value was showing result.

It is showing it like below:

but I want department to show full value as well like P has new value in sales order A4 so Department value should show the sum of ne values of any new sales order it has in it.

Is it possible to achieve it?

It was solved previously by smpa01 

Need Help

3 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    4 years ago

    Hi Junaid11 ,

     

    Try modifying the DAX as follows:

    Measure =
IF (
    CALCULATE (
        DISTINCTCOUNT ( 'Table 1'[Date] ),
        ALLEXCEPT ( 'Table 1', 'Table 1'[SalesOrder], 'Table 1'[Department] )
    ) = 1,
    SUM ( 'Table 1'[Revenue] )
)

     

    Thanks,

    Pragati

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi Junaid11 ,

         

        From your description, you should want to use a gong'neng similar to this function:

        HASONEVALUE(<columnName>)

        Try this code:

        Measure = 
IF(
    HASONEVALUE( 'Table'[Department] ),
    SUM( 'Table'[Revenue] ),
    BLANK()
)

        result:

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.