Count occurrences of value in specific columns with a measure

[powerquest1234]

Lets say I have a table that shows which businesses are currently open in which cities:

'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 =

VAR Businesses = {

( "Zoo", [Zoo] ),

( "Art Museum", [Art Museum] ),

( "Science Museum", [Science Museum] ),

( "Spa", [Spa] ),

( "History Museum", [History Museum] ),

( "Park", [Park] )

}

RETURN

CONCATENATEX (

FILTER ( Businesses, [Value2] = "Open" ),

[Value1],

", "

)

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

I believe the table is showing the incorrect values because it's returning what's in the calculated column without filtering by business type:

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? 

[tamerj1]

@powerquest1234 

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" ) )

[powerquest1234]

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.

[tamerj1]

Hi @powerquest1234 

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. 

[powerquest1234]

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.

[tamerj1]

@powerquest1234 

you are summarizing the columns by count, right?

[powerquest1234]

This is the current measure that I am trying:

Keyword Sum =

VAR OpenBusinesses =

CALCULATE(

  SUM('Sheet1'[Open businesses count])

  , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

)

RETURN

IF(

   SELECTEDVALUE('Distinct Reasons'[Value1]) = BLANK() // Condition checking what value is "Selected" by X-axis

    , OpenBusinesses

    , CALCULATE(

      SUM('Sheet1'[Open businesses count])

        , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

        , FILTER(

            ALLSELECTED('Sheet1'[City],'Sheet1'[Open businesses list])

            , COUNTROWS(

                FILTER( // Get the correct Reason

                    'Distinct Reasons'

                    , CONTAINSSTRING('Sheet1'[Open businesses list], 'Distinct Reasons'[Value1])

                )

            ) > 0 // the number of rows = # of envelopes within the count

        )

    )

)

I made a second measure that aggregates by count like so:

Keyword Count 2 =

VAR OpenBusinesses =

CALCULATE(

  COUNT('Sheet1'[Open businesses count])

  , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

)

RETURN

IF(

   SELECTEDVALUE('Distinct Reasons'[Value1]) = BLANK() // Condition checking what value is "Selected" by X-axis

    , OpenBusinesses

    , CALCULATE(

      COUNT('Sheet1'[Open businesses count])

        , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

        , FILTER(

            ALLSELECTED('Sheet1'[City],'Sheet1'[Open businesses list])

            , COUNTROWS(

                FILTER( // Get the correct Reason

                    'Distinct Reasons'

                    , CONTAINSSTRING('Sheet1'[Open businesses list], 'Distinct Reasons'[Value1])

                )

            ) > 0 // the number of rows = # of envelopes within the count

        )

    )

)

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).

[tamerj1]

@powerquest1234 

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?

 

[powerquest1234]

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:

Distinct Reasons = {

    ("Art Museum", 1)

    , ("History Museum", 2)

    , ("Park", 3)

    , ("Science Museum", 4)

    , ("Spa", 5)

    , ("Zoo", 6)

}

The Values pool uses the below measure:

Keyword Sum =

VAR OpenBusinesses =

CALCULATE(

  SUM('Sheet1'[Open businesses count])

  , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

)

RETURN

IF(

   SELECTEDVALUE('Distinct Reasons'[Value1]) = BLANK() // Condition checking what value is "Selected" by X-axis

    , OpenBusinesses

    , CALCULATE(

      SUM('Sheet1'[Open businesses count])

        , KEEPFILTERS(ALLSELECTED('Sheet1'[City]))

        , FILTER(

            ALLSELECTED('Sheet1'[City],'Sheet1'[Open businesses list])

            , COUNTROWS(

                FILTER( // Get the correct Reason

                    'Distinct Reasons'

                    , CONTAINSSTRING('Sheet1'[Open businesses list], 'Distinct Reasons'[Value1])

                )

            ) > 0 // the number of rows = # of envelopes within the count

        )

    )

)

[tamerj1]

@powerquest1234 

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" ) )

[powerquest1234]

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.

[tamerj1 · Accepted solution]

@powerquest1234 

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 )
)

[powerquest1234]

This is really incredible! Thank you so much for working through this.