Forum Discussion

mljones's avatar
mljones
Frequent Visitor
2 years ago
Solved

Count Data from Two Columns

Hello all,

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

IDProg1Prog2
123SPAN 
213ACCTSPAN
343FINA 
593BSADACCT
994ACCT

 

 

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?

Need Help
  • Sergii24's avatar
    Sergii24
    2 years ago

    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! 🙂

4 Replies

  • Sergii24's avatar
    Sergii24
    Super User
    2 years ago

    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! 🙂