cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cellison Frequent Visitor
Frequent Visitor

Dax - Ignore slicer in a measure

 

I am able to determine the value of the individual or for the group. However, when I use a slicer to choose the a person. The value that shows the average for that group title changes. I have attempted to use allexcept() and all() neither seem to have any effect on the value. I cannot specify the group and have different measures as the report will allow managers to review employees with different job titles. 

 

There are two tables involved:

tickets - which lists the individual tickets with the following information: ticket number, task, entered by, closed by, date entered, date closed

members - which lists the people who work with tickets: memberid, name, title


To determine the members tickets closed - I used the following measure:

Con Tickets Closed = 
CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
    USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date])
)

To determine the average daily tickets closed by a person, I used the following measure:

Con Avg. Daily Closed = 

VAR contbl=SUMMARIZE('Tickets',Tickets[Closed By],Tickets[Date Closed],"tkts",[Con Tickets Closed])

RETURN
AVERAGEX(contbl,[tkts])

I originally tried to use make a new measure, which was the same above but I added  
var titletkts = CALCULATE ( "tkts",ALL('Member'[Name]) 

then modified the averagex

When that did not work, I took a step back to get just the ticket count of all of the people with the same title when a person is selected with a slicer. 

Title Tickets Closed = 
CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
    USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date]),
    ALL('Member'[Name])
)

 I also tried variations of allexcept('member'[title]) - etc. 

I am still not able to get the number of tickets closed by an individual and in a seperate visual, the number of tickets closed by all of the people with the same title, when selecting the the individual's name from a slicer that uses 'member'[name]. 

Thoughts?

3 REPLIES 3
cellison Frequent Visitor
Frequent Visitor

Dax - Ignore slicer in measure on same table.

I am attempting to generate a report that shows the average value of one person's daily production vs the average daily production of all others with the same job title. 

 

I am able to determine the value of the individual or for the group. However, when I use a slicer to choose the a person. The value that shows the average for that group title changes. I have attempted to use allexcept() and all() neither seem to have any effect on the value. I cannot specify the group and have different measures as the report will allow managers to review employees with different job titles. 

 

There are two tables involved:

tickets - which lists the individual tickets with the following information: ticket number, task, entered by, closed by, date entered, date closed

members - which lists the people who work with tickets: memberid, name, title


To determine the members tickets closed - I used the following measure:

Con Tickets Closed = 
CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
    USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date])
)

To determine the average daily tickets closed by a person, I used the following measure:

Con Avg. Daily Closed = 

VAR contbl=SUMMARIZE('Tickets',Tickets[Closed By],Tickets[Date Closed],"tkts",[Con Tickets Closed])

RETURN
AVERAGEX(contbl,[tkts])

I originally tried to use make a new measure, which was the same above but I added  
var titletkts = CALCULATE ( "tkts",ALL('Member'[Name]) 

then modified the averagex

When that did not work, I took a step back to get just the ticket count of all of the people with the same title when a person is selected with a slicer. 

Title Tickets Closed = 
CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
    USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date]),
    ALL('Member'[Name])
)

 I also tried variations of allexcept('member'[title]) - etc. 

I am still not able to get the number of tickets closed by an individual and in a seperate visual, the number of tickets closed by all of the people with the same title, when selecting the the individual's name from a slicer that uses 'member'[name]. 

Thoughts?

Community Support Team
Community Support Team

Re: Dax - Ignore slicer in a measure

Hi @cellison ,

 

Please try to use the updated formula to have a try. If it doesn't meet your requirement, kindly share your pbix or sample data to me, please upload your files to One Drive and share the link here.

 

Title Tickets Closed = 
CALCULATE(
    COUNTROWS('Tickets'),
    USERELATIONSHIP('Member'[Member ID],'Tickets'[Closed By]),
    USERELATIONSHIP('Tickets'[Date Closed],'Date'[Date]),
    ALL('Member'),Values('Member'[Name])
)

Regards,

Frank

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

Re: Dax - Ignore slicer in a measure

Hi @cellison ,

 

Has your issue been solved? If so, kindly mark my answer as a solution to close the case. If any other question, feel free to let me know please.
 

Regards,
Frank

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