cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

count value with filter

Hello, 

 

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

Identification numbertravel dateReservation classTYPE
HAZERTY11/01/2019BI1st class
HAZERTY15/02/2019BI1st class
TYOPJRV12/03/2019BIMIX
TYOPJRV13/03/2019BZMIX

 

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
1 ACCEPTED SOLUTION
Microsoft
Microsoft

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

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

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

View solution in original post

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

Community Champion
Community Champion

@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] )
    )
)
Regards,
Zubair


Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors