Forum Discussion

bhavinshah's avatar
bhavinshah
Frequent Visitor
2 years ago
Solved

Group by with If Condition

Hello Team,

I have this table (Col A and Col B) and I need Output like this in Col F, G, H. 

Please help me with Power BI. 

 

Need Help
  • Musadev's avatar
    Musadev
    2 years ago

    Hi bhavinshah 
    You can use 2 measures for the new columns. check the output first and then try the DAX code for measures.
    TBL7 is my table name.

    First measure the count all the values (Y and N both)

    TBL7 Total Count = COUNTROWS(TBL7)

    After that filter out the Y rows and then count the rows.

    TBL7 Count Y = 
VAR ValuesY =
FILTER(
    TBL7,
    TBL7[Status] = "Y"
)
VAR ValuesCount =
COUNTROWS(ValuesY)
RETURN
ValuesCount+0
  • Kobe100's avatar
    Kobe100
    2 years ago

    Dear bhavinshah ,

    Please find below a possible solution.

    This measure is for the Group measure.

    Group =
    COUNTROWS('My table')

     

    This is for the Count measure.

    Count =
    VAR selItem = SELECTEDVALUE('My table'[Status])

    VAR Yes_ =
    IF(
        selItem = "N",
        0,
        CALCULATE(
            COUNTROWS('My table'),
            FILTER('My table',
            'My table'[Status] = "Y"))
    )
    RETURN

    Yes_

     
    *****Please accep this as a solution****

6 Replies

  • Musadev's avatar
    Musadev
    Icon for Resolver III rankResolver III
    2 years ago

    Hi bhavinshah 
    You can use 2 measures for the new columns. check the output first and then try the DAX code for measures.
    TBL7 is my table name.

    First measure the count all the values (Y and N both)

    TBL7 Total Count = COUNTROWS(TBL7)

    After that filter out the Y rows and then count the rows.

    TBL7 Count Y = 
VAR ValuesY =
FILTER(
    TBL7,
    TBL7[Status] = "Y"
)
VAR ValuesCount =
COUNTROWS(ValuesY)
RETURN
ValuesCount+0
    • bhavinshah's avatar
      bhavinshah
      Frequent Visitor
      2 years ago

      Hello Musadev 

       

      I need to do Group by Count first.

       

       

      Then I need to following the Data. 

       

      Thanks 

      • Musadev's avatar
        Musadev
        Icon for Resolver III rankResolver III
        2 years ago

        The first measure has grouped all the data irrespective of the status (Y,N)
        and then it applied a check to count only the Y status and ignore the N.
        You can add another measure as well to count the N values for each text, and then minus it from the total count and it will give you the same results. 

  • Kobe100's avatar
    Kobe100
    Frequent Visitor
    2 years ago

    Dear bhavinshah ,

    Please find below a possible solution.

    This measure is for the Group measure.

    Group =
    COUNTROWS('My table')

     

    This is for the Count measure.

    Count =
    VAR selItem = SELECTEDVALUE('My table'[Status])

    VAR Yes_ =
    IF(
        selItem = "N",
        0,
        CALCULATE(
            COUNTROWS('My table'),
            FILTER('My table',
            'My table'[Status] = "Y"))
    )
    RETURN

    Yes_

     
    *****Please accep this as a solution****