Forum Discussion
count value with filter
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
- 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]))RETURNIF(Count_Flight>1,"Mix","1st Class")Regards,Saurabh
3 Replies
Anonymous
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] ) ) )- 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]))RETURNIF(Count_Flight>1,"Mix","1st Class")Regards,Saurabh
