Forum Discussion

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.

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

Icon for Resolver III rank

Resolver 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
Frequent Visitor
2 years ago
Hello Musadev

I need to do Group by Count first.

Then I need to following the Data.

Thanks
- Musadev
  Resolver 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.
  - bhavinshah
    Frequent Visitor
    2 years ago
    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

Related content