Count Data from Two Columns

[mljones]

Hello all,

I've got a dataset that looks like the below:

ID	Prog1	Prog2
123	SPAN
213	ACCT	SPAN
343	FINA
593	BSAD	ACCT
994	ACCT

What I'd like to do is somehow count each instance of a Prog (regardless of Prog1 or Prog2) in a single visual. In other words, I'd like a column chart that would show SPAN as having 2 and ACCT as having 3 since they each appear in both Prog1 and Prog2 for two individuals. Or to put another way, one ID can be counted in multiple Prog counts based on the values in Prog1 and Prog2. Is there a way to combine counts from two columns in a single visual?

[Sergii24 · Accepted solution]

Hi @mljones, adding to what Greg has already suggested to you, you might want to create a separate table that contains unique values using this code:

Distinct Progr = 
    DISTINCT(
        UNION(
            DISTINCT( 'Table'[Prog1] ), 
            DISTINCT( 'Table'[Prog2] ) 
        )
    )

Now, we can use a single column from this new table (by default Prog1, but you can rename it with double-click) to show the distinct values of Prog1 and Prog2 together:

Now it's time for counting:

Count Progr = 
VAR _CurrentlySelectedProgr = SELECTEDVALUE( 'Distinct Progr'[Prog] )
VAR _CountProgr1 = 
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Prog1] = _CurrentlySelectedProgr
        )
    )

VAR _CountProgr2 = 
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Prog2] = _CurrentlySelectedProgr
        )
    )
    
RETURN _CountProgr1 + _CountProgr2

with the following output:

To conclude:

• fristly, you need a list of unique values listed within the same columns based on what you want to make a calculation (can be only column, calculated column or a column of calculated table but not measure)
• Depending on your semantic model, write a measure that will use existing relationships/lookup or filter (as in my example) to count what you want for each column separately
• sum single results

Good luck! 🙂

[mljones]

Thank you!

[Greg_Deckler]

@mljones Typically you would unpivot those columns but you can use MC Aggregations: Multi-Column Aggregations (MC Aggregations) - Microsoft Fabric Community

[mljones]

Thank you!