A function "PlaceHolder" has been used in a true/False expression that is used as a table filter .

[Anonymous]

Hi There,

I am trying to create one measure where i am tryin to get Sum of Session Duration for Distinct Session Id's for each process name .

in SQL we can do like this :
Select Session Id , Sum (Session Duration)

From XYZ Table 

where Process Name="X"

Group By Session ID 

However when i am trying to replicate same logic in DAX i am getting below mentioned error :

Session_Disticnt Count is a measure which i have created to get Distinct Values of session id's

Request anyone to help in solving this issue .

Thanks,

Ash

@amitchandak  @Ashish_Mathur 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Based on your example data, you can create measures if you want to calculate the sum of Duration for the same Process Names with the same sessionid:

Duration = CALCULATE(SUM('Table'[Session Duration]),ALLEXCEPT('Table','Table'[Process Names],'Table'[CustomTab_Item List.sessionid]))

If you want to remove duplicate values then you can create a table:

Table 2 = SUMMARIZE('Table',[Process Names],[CustomTab_Item List.sessionid],[Session Duration])

If you follow your expected results, it seems that you need to sum the Duration for a specific sessionid and de-emphasize the Duration for other sessionids. Then you can create the following measure:

Measure = 
var _A=CALCULATE(SUM('Table'[Session Duration]),ALLEXCEPT('Table','Table'[Process Names],'Table'[CustomTab_Item List.sessionid]))
var _B=MAX('Table'[Session Duration])
RETURN IF(MAX('Table'[CustomTab_Item List.sessionid]) in {"A83D7872-7F80-410C-8061-B5087F17EBDF","E38A32B6-EA28-4219-AFB4-9850243E8DA1","CDOE8AB8-D398-4336-8ADF-CB202E901DAE"},_A,_B)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hi @Anonymous ,

Thank you for your response. 

As mentioned , I need Sum of Duration for Distinct Session ID for each process Name . Basically for one process there will be 1000 Session ID's. So i want to Sum of Duration for  Distinct Session ID for each process name.

As you mentioned to create below "Measure " but you have   hardocded Session Id's but i have  more than 10000 rows with different sessions id's which is repeating many times. . So is it possible instead of using IN we can consider something where we can write In "Distinct session ID" and then calculate Sum of Sessions?

Measure =
var _A=CALCULATE(SUM('Table'[Session Duration]),ALLEXCEPT('Table','Table'[Process Names],'Table'[CustomTab_Item List.sessionid]))
var _B=MAX('Table'[Session Duration])
RETURN IF(MAX('Table'[CustomTab_Item List.sessionid]) in {"A83D7872-7F80-410C-8061-B5087F17EBDF","E38A32B6-EA28-4219-AFB4-9850243E8DA1","CDOE8AB8-D398-4336-8ADF-CB202E901DAE"},_A,_B)

I hope i clariifed my requirements

Thanks,

Ash

[Anonymous]

Hi @Anonymous ,

Please refer to my reply above and create this measure if you wish to calculate the sum:

Duration = CALCULATE(SUM('Table'[Session Duration]),ALLEXCEPT('Table','Table'[Process Names],'Table'[CustomTab_Item List.sessionid]))

The total number of Duration for process with the name Bulk Skilling is 342.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[ashcool_1006]

Session_Duration = CALCULATE(Max('Table'[Session Duration]),ALLEXCEPT('Table','Table'[Process Names],'Table'[CustomTab_Item List.sessionid]))"

Now i just want the  Sum (Max_Duration) measure but i dont want to use any SUMX as i am just looking for Sum of resultant value  in a measure form .

Is there any way we can create this ?

Regards,

Ash

[Ashish_Mathur]

Hi,

Share some data, explain the question in simple English and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[ashcool_1006]

Hi @Ashish_Mathur 
Thank you for your response.

i dont have permissions to upload the data. Please see below sample data with expected result.

I hope this helps .

Hope you can share some inputs on this. My Process name column is in text Format.

Regards,

ash

[Ashish_Mathur]

Hi,

Without a file i really cannot help.  Anyways, drag the first 2 columns to a table/matrix visual and write this measure

Measure = sum(Data[session duration])

Hope this helps.

[Anonymous]

Hi @Ashish_Mathur ,

Thanks for the solution. Will try .

Regards,

Ash

[Anonymous]

Hi @Dangar332 ,

Thank you for giving this solution however as sson as in added Filter in the measure i am getting below error

My PROCESS name column is in text format only.

Any help in solving this would be really helpful.

Regards,

Ash

[Dangar332]

Hi, @Anonymous 

From your sql explanation below measure can fulfill you requirment.

session_duration_2=
calculate(
   SUM('CustomTab_Detailed Process View'[Session Duration]),
   value('CustomTab_Detailed Process View'[Process names])
)

If there any other creiteria also apllied then please provide some dummy data

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @Dangar332 ,

Appreicate your quick resposne . I understand what you trying to say but there is one more condition applied which is Distinct(sESSION .id) For that i am getting trouble.

Let me know if you can sugget any thing.

Regards,

Ash

[Kedar_Pande]

@Anonymous 

You can try:

Session_Duration = 
SUMX(
VALUES('CustomTab_Detailed Process View'[sessionid]),
CALCULATE(
SUM('CustomTab_Detailed Process View'[Session Duration]),
'CustomTab_Detailed Process View'[Process Names] = "YourDesiredProcessName"
)
)

💡 If this helped, please give Kudos 👍 or mark it as a Solution ✅.
Best regards,
Kedar
🌐 Connect on LinkedIn

[Anonymous]

Hi @Kedar_Pande ,

I Tried this way which you suggested but its giving me wrong value. Its just doing the sum of Duration Session without checking Distinct Value of Session.id for each process name .

Basically its bypassing the filter "Distinct". 

I tired one more way where its working on Distinct Filter but It gives me this error however i am not sure as soon as i use Filter function for Distinct value it gives me this Format error where my process name is in text format and its working fine if I use the above formula which you suggested above.

Can you help me in solving this ?

Thanks,

Ash

[Dangar332]

Hi, @Anonymous 

Wrape your measure indisde Filter Function like below and use it insted of Measure

"Filter ('Your table name',[Your meaure])"

Best Regards,
Dangar

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.