Count occurrences of value in specific columns

[Anonymous]

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

City	Zoo	Spa	Art Museum	Science Museum	History Museum	Park
El Paso	OPEN	CLOSE	CLOSE	CLOSE	NA	CLOSE
Anchorage	CLOSE	CLOSE	OPEN	OPEN	NA	CLOSE
San Francisco	OPEN	CLOSE	CLOSE	CLOSE	NA	NA
Cleveland	OPEN	CLOSE	OPEN	CLOSE	OPEN	NA
Boston	CLOSE	CLOSE	CLOSE	CLOSE	CLOSE	CLOSE
Atlanta	OPEN	OPEN	CLOSE	NA	NA	NA
Honolulu	OPEN	OPEN	NA	CLOSE	NA	CLOSE
Dallas	CLOSE	OPEN	CLOSE	OPEN	NA	CLOSE

Lets imagine that I want to add two DAX Calculated columns with the following information:

- Desired Column 1: Count of Open businesses (a count of the value "OPEN" in the columns Zoo, Spa, Art Museum, Science Museum, History Museum, Park for each particular city)

- Desired Column 2: List of Open Businesses (lists the column names on this row that have the value "OPEN")

City	Zoo	Spa	Art Museum	Science Museum	History Museum	Park	Desired Column: Count of Open Businesses	Desired Column 2: List of Open Businesses
El Paso	OPEN	CLOSE	CLOSE	CLOSE	NA	CLOSE	1	Zoo
Anchorage	CLOSE	CLOSE	OPEN	OPEN	NA	CLOSE	2	Art Museum, Science Museum
San Francisco	OPEN	CLOSE	CLOSE	CLOSE	NA	NA	1	Zoo
Cleveland	OPEN	CLOSE	OPEN	CLOSE	OPEN	NA	3	Zoo, Art Museum, History Museum
Boston	CLOSE	CLOSE	CLOSE	CLOSE	CLOSE	CLOSE	0	None
Atlanta	OPEN	OPEN	CLOSE	NA	NA	NA	2	Zoo, Spa
Honolulu	OPEN	OPEN	NA	CLOSE	NA	CLOSE	2	Zoo, Spa
Dallas	CLOSE	OPEN	CLOSE	OPEN	NA	CLOSE	2	Spa, Science Museum

I am able to do this in Microsoft Excel but I'm not sure if this is possible with DAX. 

[tamerj1 · Accepted solution]

Hi @Anonymous 

please try the following 

Count of Open businesses =
VAR Businesses = {
( "Zoo", [Zoo] ),
( "Art", [Art] ),
( "Museum", [ Museum] ),
( "Science", [Science] ),
( "Spa", [Spa] ),
( "History", [History] ),
( "Park", [Park] )
}
RETURN
COUNTROWS ( FILTER ( Businesses, [Value2] = "Open" ) )

List of Open businesses =
VAR Businesses = {
( "Zoo", [Zoo] ),
( "Art", [Art] ),
( "Museum", [ Museum] ),
( "Science", [Science] ),
( "Spa", [Spa] ),
( "History", [History] ),
( "Park", [Park] )
}
RETURN
CONCATENATEX (
FILTER ( Businesses, [Value2] = "Open" ),
[Value1],
UNICHAR ( 10 )
)

[Anonymous]

Thank you! This works if I add these codes as calculated columns. This was what I ended up using:

Open businesses count =

VAR Businesses = {

( "Zoo", [Zoo] ),

( "Art", [Art Museum] ),

( "Science", [Science Museum] ),

( "Spa", [Spa] ),

( "History", [History Museum] ),

( "Park", [Park] )

}

RETURN

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

Open businesses list =

VAR Businesses = {

( "Zoo", [Zoo] ),

( "Art", [Art Museum] ),

( "Science", [Science Museum] ),

( "Spa", [Spa] ),

( "History", [History Museum] ),

( "Park", [Park] )

}

RETURN

CONCATENATEX (

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

[Value1],

", "

)

I am really excited that you can use variables in this way.

To make sure I understand what you did for each function:

1) You created a variable and you created a virtual DAX table to store within that variable. When creating the new table, you turned each column into a row using DAX table creation syntax. 

2) For the Return, you then used a function to count the number of "Open" values or to grab the name of the columns that had open values.

This is my screenshot of the resulting columns for anyone who is curious:

I'm amazed that Power BI/DAX is able to count the correct amounts for each city rows, because the virtual tables don't seem to store the city information and the return doesn't seem to reference the original table that has the city information (unless they do on the back-end?).

[tamerj1]

@Anonymous 
What you have stated above is 100% correct.

Regarding your last paragraph, When creating a calculated column there is an active row context in the background. So for each row or the table we are creating a two column virtual table that contains the [Business Type] and [Status]. In other words, the DAX code actually unpivots the table for each row.

Just noticed that the formula requires fine tuning. The following formula orders the businesses in ascending order

Open businesses list =
VAR Businesses = {
    ( "Zoo", [Zoo] ),
    ( "Art", [Art Museum] ),
    ( "Science", [Science Museum] ),
    ( "Spa", [Spa] ),
    ( "History", [History Museum] ),
    ( "Park", [Park] )
}
RETURN
    CONCATENATEX (
        FILTER ( Businesses, [Value2] = "Open" ),
        [Value1],
        ", ",
        [Value1], ASC
    )

[Jihwan_Kim]

Hi,

If you can use Power Query Editor, I suggest unpivotting the table like below, load it, and then create a new table by using DAX formula.

New Table = 
ADDCOLUMNS (
    VALUES ( Data[City] ),
    "@Count of Open Business",
        CALCULATE ( COUNTROWS ( FILTER ( Data, Data[Status] = "OPEN" ) ) ) + 0,
    "@List of Open Business",
        VAR _result =
            CALCULATE (
                CONCATENATEX ( FILTER ( Data, Data[Status] = "OPEN" ), Data[Category], ", " )
            )
        RETURN
            IF ( _result = BLANK (), "None", _result )
)

[Anonymous]

Thank you so much for your response. I do think this would work as a solution but unfortunately I don't think I would be allowed to alter the source table in power query since multiple reports use this dataset. I should have mentioned this in the initial post and I apologize.