Group by with If Condition

[bhavinshah]

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. 

[Musadev · Accepted solution]

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]

Hello @Musadev  @Kobe100 

Thanks for helping. Both solutions are working properly. 

[Kobe100 · Accepted solution]

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****

[Musadev · Accepted solution]

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]

Hello @Musadev 

I need to do Group by Count first.

Then I need to following the Data. 

Thanks 

[Musadev]

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. 

[bhavinshah]

Hello @Musadev 

This works - 

TBL7 Total Count = COUNTROWS(TBL7)

 Original 

Now how can I apply Y then Count should be there Else 0   - How CAn I apply