total in table is correct but the values is not

[newbie_powerbi]

I need distinct count of the persons whose tickets are open

if there are 2 open tickets for the same person then I need to consider only the latest open date ticket (max date )

https://drive.google.com/file/d/1G_sfyGqbAyCbxA359lShcC6z06zDWYIA/view?usp=share_link 

[Ahmedx]

maxDateforopen_dt3 = 
VAR  _Comulitiv = TOTALYTD([open ticket count],'Table'[open_dt])
RETURN
   IF( _Comulitiv>1,SELECTEDVALUE('Table'[open_dt]))

[Ahmedx]

maxDateforopen_dt2 = 
VAR _DateOpen =MAX('Table'[open_dt])
VAR _SD =[open ticket count]
VAR _MaxDate=
IF ( _SD>0,
        CALCULATE (
        MAX ( 'Table'[open_dt] ),
        ALLEXCEPT ( 'Table', 'Table'[person], 'Table'[close_dt] )
    )
)
RETURN
IF(_DateOpen=_MaxDate,_MaxDate)

[newbie_powerbi]

this is working fine but some rows it shows blank values where it should show max date

I don't know why

I feel like I need a dax that has the characteristics of allselected & removefilters 

when I use allselected, it show max_date for all rows

when I use removefilters, it perfect but in some cases, some rows are blank

[Ahmedx]

show me where it happens

and there your measure (open ticket count) shows 1 ?

[newbie_powerbi]

yes, it has open ticket count as 1

my dax formula & procedure are same but dataset is large & has mutiple conditions to identify open

that's the only difference

my data is around 10 million rows

I'm trying to find a large public dataset so I could replace it & show if that instance repeat

[palpeti21]

You can consider the following for the Open Ticket Count:

OpenTicketCount =

VAR OpenTicekts =

    SELECTCOLUMNS(

        FILTER(

            'Table',

            ISBLANK('Table'[close_dt])

        ),

        "Person", [person],

        "Open Date", [open_dt],

        "Close Date", [close_dt]

    )

VAR UniqueNames = SUMMARIZE(OpenTicekts, [Person])

RETURN COUNTROWS(UniqueNames) 

And the following to display the date of latest open ticket for all lines where there is a ticket open:

DateOfMax =

IF(

    ISBLANK(

        MAX('Table'[close_dt])

    ),

    CALCULATE(  

        MAX('Table'[open_dt]),

        REMOVEFILTERS('Table'[open_dt]),

        ISBLANK('Table'[close_dt])

    )

)

[newbie_powerbi]

@palpeti21 thank you very much 

but open ticket logic has mutiple conditions, to simply I just added one condition

I need to get the same result using open ticket count measure

[Ahmedx]

Is this what you are looking for?
https://1drv.ms/u/s!AiUZ0Ws7G26RhhSNKwaXFZJGwJjb?e=l5FEVB

[newbie_powerbi]

@Ahmedx thank you very much, that's exactly what I'm expecting but you are using open column which is actually not available (it was created for understanding purpose), I have created a measure called Open ticket count, that can be used

this is the result I'm expecting but not using open column but using open ticket count measure

[Ahmedx]

and so?

maxDateforopen_dt = 
VAR _DateOpen =MAX('Table'[open_dt])
VAR _Dateclose =MAX('Table'[close_dt])
VAR _MaxDate=
IF ( ISBLANK(_Dateclose),
        CALCULATE (
        MAX ( 'Table'[open_dt] ),
        ALLEXCEPT ( 'Table', 'Table'[person], 'Table'[close_dt] )
    )
)
RETURN
IF(_DateOpen=_MaxDate,_MaxDate)