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

Measure to include a filtered out value

I am currently attempting to create a measure that returns a calculated value based on a certain role and type. There are two roles, each role is associated with numerous types. I am trying to forcefully include a certain type within a certain role even though it is associated with a different role. Please refer to the example below for further clarification.

 

Role     Type            Value
Dp1      jacket          50

Dp2      watch          20

Dp2     shorts          15

Dp2     socks           5

Dp1     sandals        25

Dp1     pants           20

Dp1     shirt            10

 

Lets say, I wanted to create this measure for all of "Dp1" but include the "watch" from "Dp2." There measure should then return the count of the total items. In this case, 5. As there are 4 items from "Dp1" + the "watch" from "Dp2." Please note, in my data there are numerous duplicates of each type and role. All I need is a measure that calculates the total number of rows.

 

Now I could manually go in and define each type I want the measure to include, the problem with that is in the future, new types most likely will be added to each role. So having to manually define each type to include is not a valid solution. 

 

Associated to this problem is a measure I created to filter out a certain type from a certain role. This measure works as intended. You can see this below.

Measure = CALCULATE(DISTINCTCOUNT(Table1[Value]), , FILTER(Table1, Table1[Role] = "Dp2",) FILTER(Table1, Table1[Type] <> "watch"))

 

Please note, I am working inside of a dataset and cannot access the features to create calculated columns. Otherwise, that would be my go to method. Also, I intend to display this measure on a graph, each column showing the value by type.

 

Any assistance on this measure would be greatly helpful.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Warthog710
Frequent Visitor

Re: Measure to include a filtered out value

Actually, I seem to have found my own solution. I created two measures, one that calculates the value of the type that I want to include.

 

filteredVal = CALCULATE(DISTINCTCOUNT(Table1[Value]), FILTER(Table1, Table1[Type] = "watch"))

 

I then created another measure, and added this measure to the end.

 

totalCount = CALCULATE(DISTINCTCOUNT(Table1[Value]), FILTER(Table1, Table1[ROLE] = "Dp1")) + [filteredVal]

 

Funny enough, when I display this measure in a graph, without any filters, and tell it to only show items that are >= 1. It shows columns for all the types associated with Dp1 and the included type from the other role. I personally did not expect this behavior, but am glad for it.

View solution in original post

1 REPLY 1
Warthog710
Frequent Visitor

Re: Measure to include a filtered out value

Actually, I seem to have found my own solution. I created two measures, one that calculates the value of the type that I want to include.

 

filteredVal = CALCULATE(DISTINCTCOUNT(Table1[Value]), FILTER(Table1, Table1[Type] = "watch"))

 

I then created another measure, and added this measure to the end.

 

totalCount = CALCULATE(DISTINCTCOUNT(Table1[Value]), FILTER(Table1, Table1[ROLE] = "Dp1")) + [filteredVal]

 

Funny enough, when I display this measure in a graph, without any filters, and tell it to only show items that are >= 1. It shows columns for all the types associated with Dp1 and the included type from the other role. I personally did not expect this behavior, but am glad for it.

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors