Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
newbie_powerbi
Helper II
Helper II

total in table is correct but the values is not

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 

10 REPLIES 10
Ahmedx
Super User
Super User

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

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)

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

show me where it happens

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

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
Frequent Visitor

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])
    )
)
 
icnup.JPG

 

 



@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
Super User
Super User

@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

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)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.