Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
'Businesses'
City | Zoo | Spa | Art Museum | Science Museum | History Museum | Park |
El Paso | OPEN | |||||
Anchorage | OPEN | OPEN | ||||
San Francisco | OPEN | |||||
Cleveland | OPEN | OPEN | OPEN | |||
Boston | ||||||
Atlanta | OPEN | OPEN | ||||
Honolulu | OPEN | OPEN | ||||
Dallas | OPEN | OPEN |
I also have another table 'Distinct Business table' with a field [Distinct Business Type Column] that is just a distinct list of the businesses:
'Distinct Business table'
[Distinct Business Type Column]
Zoo |
Spa |
Art Museum |
Science Museum |
History Museum |
Park |
In my previous topic, I learned how to create calculated columns that would count the open businesses in each city and would also list out the open businesses like so.
This is how I've applied it for my situation:
List of Open Businesses =
Count of Open Businesses =
IF(ISBLANK('Businesses'[List of Open Businesses])
, 0
,LEN('Businesses'[List of Open Businesses])-LEN(SUBSTITUTE('Businesses'[List of Open Businesses],",",""))+1
)
City | Zoo | Spa | Art Museum | Science Museum | History Museum | Park | Count of Open Businesses | List of Open Businesses |
El Paso | OPEN | 1 | Zoo | |||||
Anchorage | OPEN | OPEN | 2 | Art Museum, Science Museum | ||||
San Francisco | OPEN | 1 | Zoo | |||||
Cleveland | OPEN | OPEN | OPEN | 3 | Zoo, Art Museum, History Museum | |||
Boston | 0 | None | ||||||
Atlanta | OPEN | OPEN | 2 | Zoo, Spa | ||||
Honolulu | OPEN | OPEN | 2 | Zoo, Spa | ||||
Dallas | OPEN | OPEN | 2 | Spa, Science Museum |
However, my end user also wants me to create a table visual that can be filtered to a city or multiple cities and accurately show the count of open businesses.
I have attempted to create this table by placing the 'Distinct Business table'[Distinct Business Type Column] in the "Columns" field. When I put the Count of Open Businesses calculated column into the Values field, unfortunately, it just returns the count of businesses without splitting by business type. For example, if we filtered this table to El Paso (where only the zoo is open) and Anchorage (where only the Art Museum and Science Museum is open), we get the following:
Multiple Cities Visual (current result)
Zoo | Spa | Art Museum | Science Museum | History Museum | Park | Total Count of Open Businesses |
1 | 2 | 2 | 3 |
Ideally, the table should show this:
Multiple Cities Visual (Desired result)
Zoo | Spa | Art Museum | Science Museum | History Museum | Park | Total Count of Open Businesses |
1 | 1 | 1 | 3 |
City | Zoo | Spa | Art Museum | Science Museum | History Museum | Park | Count of Open Businesses | List of Open Businesses |
El Paso | OPEN | 1 | Zoo | |||||
Anchorage | OPEN | OPEN | 2 | Art Museum, Science Museum |
It sounds like a measure might be better for the Multiple Cities Visual than a calculated column, because a measure can be filtered. However, I am getting error messages when I try to build the measure. It seems like I cannot add a column that counts open businesses to a table variable and then reference those counts.
Is there a way that I can get around this?
Solved! Go to Solution.
If you want to unpivot the table inside a measure you can follow the example in the attached sample file. Of course the real data would be different and hence might require some modifications.
Count Open =
SUMX (
VALUES ( 'Distinct Reasons'[Value2] ),
VAR T1 = Sheet1
VAR Art = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Art Museum], "@Bussiness", 1 )
VAR History = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[History Museum], "@Bussiness", 2 )
VAR Park = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Park], "@Bussiness", 3 )
VAR Science = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Science Museum], "@Bussiness", 4 )
VAR Spa = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Spa], "@Bussiness", 5 )
VAR Zoo = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Zoo], "@Bussiness", 6 )
VAR T2 = UNION ( Art, History, Science, Park, Spa, Zoo )
VAR T3 = FILTER ( T2, [@Status] = "Open" && [@Bussiness] = 'Distinct Reasons'[Value2] )
RETURN
COUNTROWS ( T3 )
)
In this case you would need a measure for each business type like
Count Open Art Museum = COUNTROWS ( FILTER ( VALUES ( 'Table'[Art Museum] ), 'Table'[Art Museum] = "Open" ) )
For more examples:
Here, the current filter is Dallas. It should say "1" for Science Museum and "1" for Spa, with a total of 2.
Here, the current filter is for Dallas and El Paso. The bottom table with the reasons list should say "1" for the Science Museum, 1 for the Spa and 1 for the zoo.
Here, the current filter contains Honolulu, Dallas and El Paso. The total is correct on the rows (2 for Dallas, 1 for El Paso, and 2 for Honolulu), but the column totals are incorrect--Spa should be two because only two cities have a Spa open (Honolulu and Dallas), Zoo should be 2 (only El Paso and Honolulu have open zoos) and the Science Museum should be 1 because only Dallas has an open Science Museum.
One possibility could be that the blanks in your data are actually empty strings which are counted as cells with value. You may open the query editor and double check that the empty cells show "null" in power query. If not you can easily replace the empty string with null.
I don't think that is it. I tried replacing the empty strings with nulls, but I am still getting the same result below:
Multiple Cities Visual (current result)
Zoo | Spa | Art Museum | Science Museum | History Museum | Park | Total Count of Open Businesses |
1 | 2 | 2 | 3 |
I think the problem is that because the calculated column has a count of open businesses for city, it is returning "2" for any business associated with Anchorage in our visual (which is currently filtered to Anchorage and El Paso). However, it needs to return "1" under Art Museum and "1" Science Museum.
This is the current measure that I am trying:
I made a second measure that aggregates by count like so:
As you can see, the sum gets to the correct count of open businesses per city in a table that has rows for each city. The count does not.
However, in a table that doesn't separate the cities, I notice that the count gets to the correct row results (Dallas and Honolulu both have open Spas for a count of 2, Honolulu and El Paso both have open Zoos for a count of 2, and Dallas is the only city with an open Science Museum for a count of 1) but only the sum gets to the correct aggregate (2 + 2 + 1 = 5).
Thank you for the brief explanation anfor yoy patience.
in order not to create some confusion around please concentrate on answering my specific questions rather than explaining your attempted solution.
How did you get these numbers?
This is a table visual with teh following parameters.
The rows pool uses the City field in the businesses table.
The columns pool uses 'Distinct Reasons'[Value1], a disconnected table I made. Here is the code for that table:
The Values pool uses the below measure:
The first step should be to unpivot the table using power query. Once you have the business types in one column side by side with the Status then everything else will be much easier. You can just place the cityin the rows of the matrix, the business type in the columns of tge matrix and in values the following measure
countOpen =
COUNTROWS ( FILTER ( 'Table', 'Table'[Status] = "Open" ) )
Is there a way I could pivot it as a table variable within a measure? I am not allowed to edit the dataset or create a new dataset in order to pivot thist able.
If you want to unpivot the table inside a measure you can follow the example in the attached sample file. Of course the real data would be different and hence might require some modifications.
Count Open =
SUMX (
VALUES ( 'Distinct Reasons'[Value2] ),
VAR T1 = Sheet1
VAR Art = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Art Museum], "@Bussiness", 1 )
VAR History = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[History Museum], "@Bussiness", 2 )
VAR Park = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Park], "@Bussiness", 3 )
VAR Science = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Science Museum], "@Bussiness", 4 )
VAR Spa = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Spa], "@Bussiness", 5 )
VAR Zoo = SELECTCOLUMNS ( T1, "@City", Sheet1[City], "@Status", Sheet1[Zoo], "@Bussiness", 6 )
VAR T2 = UNION ( Art, History, Science, Park, Spa, Zoo )
VAR T3 = FILTER ( T2, [@Status] = "Open" && [@Bussiness] = 'Distinct Reasons'[Value2] )
RETURN
COUNTROWS ( T3 )
)
This is really incredible! Thank you so much for working through this.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |