cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wsushox1 Regular Visitor
Regular Visitor

Creating unique value column based on two filters

Hello all!

 

I have what I believe to be a straight forward problem but really struggling with the solution.

 

I want to get the number of active (open) franchises in a region and create a new column with that information.  I can do this is Python relatively easy but I'm trying to do as much in PowerBI/DAX as I can. So for example, a sample table (Table1) is below:

 

RegionFranchise #Open
New England111Yes
Arkoma211Yes
Magnolia311No
Magnolia312Yes
New England112No
Arkoma212Yes
Arkoma213No
Arkoma214Yes
New England113No
Magnolia313Yes

 

I have another table that has as many rows as their are regions - in this case lets assume I have the three regions listed. So what I want to do is filter based on the unique regions and then count the number of entries in that filtered region that are == "Yes".  (Get the number of active franchises in each region).

 

ActiveFranchisesPerRegion = CALCULATE(COUNTA('Table1'[Open]), FILTER('Table1', DISTINCT('Table1'[Region]) ))

 

However I'm getting the error: 'A table of multiple values was supplied where a single value was expected'.  I'm not surprised to get the error as I know the code and filters are not right.  

 

My expected result would be:

RegionActiveFranchisesPerRegion
New England1
Arkoma3
Magnolia2

 

Can anyone help to set up my filters/code in the right way to help address this problem?

 

fdasfasf

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Creating unique value column based on two filters

Hi @wsushox1 

1. Place Table1[Region] in the rows of a table visual

2. CREATE this measure and place it in the visual

Measure = CALCULATE( DISTINCTCOUNT( Table1[Franchise #] ), Table1[Open] = "Yes")

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

   

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Creating unique value column based on two filters

Hi @wsushox1 

1. Place Table1[Region] in the rows of a table visual

2. CREATE this measure and place it in the visual

Measure = CALCULATE( DISTINCTCOUNT( Table1[Franchise #] ), Table1[Open] = "Yes")

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

   

View solution in original post

wsushox1 Regular Visitor
Regular Visitor

Re: Creating unique value column based on two filters

Perfect.  I knew it was simple, just needed som help.


Thank you!

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors