Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
maxDateforopen_dt3 =
VAR _Comulitiv = TOTALYTD([open ticket count],'Table'[open_dt])
RETURN
IF( _Comulitiv>1,SELECTEDVALUE('Table'[open_dt]))
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
You can consider the following for the Open Ticket Count:
@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
Is this what you are looking for?
https://1drv.ms/u/s!AiUZ0Ws7G26RhhSNKwaXFZJGwJjb?e=l5FEVB
@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)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |