Creating unique value column based on two filters

[Anonymous]

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:

Region	Franchise #	Open
New England	111	Yes
Arkoma	211	Yes
Magnolia	311	No
Magnolia	312	Yes
New England	112	No
Arkoma	212	Yes
Arkoma	213	No
Arkoma	214	Yes
New England	113	No
Magnolia	313	Yes

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:

Region	ActiveFranchisesPerRegion
New England	1
Arkoma	3
Magnolia	2

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

fdasfasf

[AlB · Accepted solution]

Hi @Anonymous 

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  

[Anonymous]

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

Thank you!