Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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
AlB
Super User
Super User

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  Datanaut

   

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

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  Datanaut

   

Anonymous
Not applicable

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


Thank you!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.