Forum Discussion

CJ_96601's avatar
CJ_96601
Helper V
6 years ago
Solved

Measure - If then else

Can someone help me to calculate each record with F as 2 count and M as one count, please refer below. 

 

Record     
      
F     
F     
F     
F     
M     
M     
M     
      
Total count 11    
      
Note:  Each record with F is equivalent to 2 and M is equivalent to 1 
Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi CJ_96601 ,

    I have crate an index column first in query editors to count conveniently.

    Then you can create a measure like this:

    Total count =
CALCULATE (
    COUNT ( 'Table'[Record] ),
    FILTER ( 'Table', 'Table'[Record] = "F" )
) * 2
    + CALCULATE (
        COUNT ( 'Table'[Record] ),
        FILTER ( 'Table', 'Table'[Record] = "M" )
    )

    You will get the result:

     

    Here is the demo, please try it:

    PBIX 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    Hi CJ_96601 ,

    I have crate an index column first in query editors to count conveniently.

    Then you can create a measure like this:

    Total count =
CALCULATE (
    COUNT ( 'Table'[Record] ),
    FILTER ( 'Table', 'Table'[Record] = "F" )
) * 2
    + CALCULATE (
        COUNT ( 'Table'[Record] ),
        FILTER ( 'Table', 'Table'[Record] = "M" )
    )

    You will get the result:

     

    Here is the demo, please try it:

    PBIX 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    CJ_96601 


    you can have column like this and sum
    new Column = If(Table[Record]="F",2,1)
    or

    sumx(values(Table[Record]),If(Table[Record]="F",2,1))
    or
    sumx(selectcolumns(Table,"ID",Table[record],"_sum",If(Table[Record]="F",2,1)),[_sum])

    • CJ_96601's avatar
      CJ_96601
      Helper V
      6 years ago

      Thanks.

       

      I want to count the records as follows:

       

         F

         F

         M

       

      Based on the below sample.  Total count is 5