Case when in dax

[Anonymous]

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_invoice_no else null end)) as tickets

to a dax measure. Can this be done?

[v-eachen-msft]

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 ()
    )

[Gordonlilj · Accepted solution]

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 )