Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
8 years ago

Counting certain values in different Columns

Hi All,

 

I am very new to Power BI and still exploring it.  Suppose we have the following data

 

Name    Country       Col1    Col2     Col3

A              X                 cs        ps

B              Y                 ps       ip          cs

C              Z                 ip

 

I want to count how many there is a cs ,ps and ip. How can I do it with DAX?

 

Many thanks

 

 

Need Help

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    abukapsoun,

     

    You need to unpivot columns.

     

    Results

     

    Then create measure like below.
    Countcs = CALCULATE(COUNTROWS(FILTER(Table1,Table1[Value]="cs")))

     

    Regards,

    Charlie Liao

    • abukapsoun's avatar
      abukapsoun
      Post Patron
      8 years ago

      v-caliao-msft

       

      Thank you very much! That worked perfectly for me. 

       

      I have 2 questions: 

       

      When i have applied the transformating, new columns are created (Attribute, Value) and the original one disssapeared. Is there a way to keep the original columns as well ? And then if I want to modify the data source with some new entry in Col1, how will I be able to do that?

       

      When I tried to apply a stacked column chart by putting Col1 Col2 Col3 in the Axis, and the count of Col1 Col2 Col3 in the Values, the count didnt look right. Why? For example please check attached below the "Volte" count