Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Case when in dax

I want to convert  count(distinct(case when invoice_type_id in (1,3,5,9) then sys_invoice_no else null end)) - count(distinct(case when invoice_type_id in (7,14) and full_return_flag = 1 then sys_...
Tutorial Requests
  • Gordonlilj's avatar
    Gordonlilj
    6 years ago

    Hi,

     

    I'm not sure if i've read your code correctly but something like this might work

    Tickets = 
              CALCULATE( DISTINCTCOUNT( 'Table'[sys_invoice_no] ), 
                         'Table'[invoice_type_id] IN ( {1,3,5,9} ) ) - 

              CALCULATE( DISTINCTCOUNT( 'Table'[sys_invoice_no] ), 
                         'Table'[invoice_type_id] IN ( {7,14} ),
                         'Table'[full_return_flag] = 1 )

     

v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You could try this DAX and tell me if it works.

tickets =
SWITCH (
    TRUE (),
    'Table'[invoice_type_id] IN { 1, 3, 5, 9 }, DISTINCTCOUNT ( 'Table'[sys_invoice_no] ),
    BLANK ()
)
    - SWITCH (
        TRUE (),
        AND ( 'Table'[invoice_type_id] IN { 7, 14 }, 'Table'[full_return_flag] = 1 ), DISTINCTCOUNT ( 'Table'[sys_invoice_no] ),
        BLANK ()
    )