count value with filter

[Jibril]

Hello, 

i have a data table with the 3 colums and i hope to get the last one:

Identification number	travel date	Reservation class	TYPE
HAZERTY	11/01/2019	BI	1st class
HAZERTY	15/02/2019	BI	1st class
TYOPJRV	12/03/2019	BI	MIX
TYOPJRV	13/03/2019	BZ	MIX

i have every time 2 lines for each Iden. number, for the earlier date of the identification number i have always BI but for the return (the later date of the identification class) i can find BI or BZ. if i have 2 times BI for the same identification number i want to call it 1st class and if i have another value for the return i want to call it mix

i am looking to get the colonne TYPE but i just can't find the way after spending few hours with this. 

I tried a :

CALCULATE(DISTINCTCOUNT('A_R MALIN'[Reservation_class]);FILTER('A_R MALIN';'A_R MALIN'[identification number]='A_R MALIN'[identification number])) 

I get 4 (it's counting all the different values in the columns and not for each identification number even if i use FILTER) while i'm trying to get 1 or 2

i really hope someone can help me with this

thx

[saurabh_kedia_ · Accepted solution]

Hi Jibril,

Kindly try the below DAX, if it works accept this as solution & give kudos!

Type =

VAR Count_Flight = CALCULATE(DISTINCTCOUNT('A_R MALIN'[Reservation Class]),ALLEXCEPT('A_R MALIN','A_R MALIN'[Identification Number]))

RETURN

IF(Count_Flight>1,"Mix","1st Class")

Regards,

Saurabh

[Jibril]

thank you it work, didn't know before the all except!! 🙂

[Zubair_Muhammad]

@Jibril 

Seems you are just missing the earlier function

Column =
CALCULATE (
    DISTINCTCOUNT ( 'A_R MALIN'[Reservation class] ),
    FILTER (
        'A_R MALIN',
        'A_R MALIN'[identification number]
            = EARLIER ( 'A_R MALIN'[identification number] )
    )
)