NEED HELP ON DAX: COUNT A COLUMN BASED ON 2 OR MORE CONDITIONS

[Anonymous]

Hello, I am new to PowerBI but I have been working on Microsoft Excel so I have an idea how to do it on Excel but I'm having an issue executing it on PowerBI. I hope you can help me.

Basically, I'm working on data that looks like this:

Column 1	Column 2	Column 3
Row 1	2	Red
Row 2	1	Red
Row 3	3	Red
Row 4	1	Red
Row 1	2	Blue
Row 2	1	Blue
Row 3	3	Blue
Row 4	1	Blue

I need a PowerBI formula that says IF Column 2 = 1, COUNT Column 1, FILTER BY Red ; IF Column 2 =2 , COUNT Column 1 DIVIDED BY 2, FILTER BY Red, IF Column 2=3, COUNT Column 1 DIVIDED BY 3, FILTER BY Red.

Basically, I'm using Column 2 as a category and a condition to dictate how to count the Column 1.

I hope my questions make sense and thank you for your help!

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your desired output looks like, but please check the below picture and the attached pbix file.

Expected result: =

SWITCH (

SELECTEDVALUE ( Data[Column 2] ),

1,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ),

2,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ) / 2,

3,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ) / 3

)

[Anonymous]

Thank you, JiHwan and Margarita. I really appreciate your help on this. Both of the solution works! 

[MargaritaG · Accepted solution]

Hey, if you are trying to achieve that calculation would always divide by the value of Column2 then you could try this:

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your desired output looks like, but please check the below picture and the attached pbix file.

Expected result: =

SWITCH (

SELECTEDVALUE ( Data[Column 2] ),

1,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ),

2,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ) / 2,

3,

COUNTROWS ( FILTER ( Data, Data[Column 3] = "Red" ) ) / 3

)