- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How to use this CASE statement to DAX or to a Cust...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

BBIUser

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018
12:53 PM

Hi,

Having trouble to get the result out of these CASE statements. Sample data is attached below.

1)

sum(case when Column1='Yes' or Column2='Yes')then 1 else 0 end) TotalValBranches

2)

sum(case when (Column1 = 'Yes' or Column2 = 'Yes') and Current=0 and Expected=0 then 1 else 0 end) TotalAttBranches

3)

sum(case when Coulmn3 = 'Yes' then 1 else 0 end) TotalChBranches

Ho do I use these CASE statements as a DAX? I will be counting the Total row count and might use it as a card visual.

Also, is it better to create a calculated Measure from these CASE statements or a Custom Column and then use that as a DAX?

Thanks in Advance!

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

Accepted Solutions

MFelix

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-11-2018
02:16 PM

Hi @BBIUser,

You are correct this ask for a table if want to apply any filters you should wrap your column in a ALLSELECTED if you want an overall result the use the ALL function.

In the case the measure would be something like this:

TotalValBranches = calculate (countrows(ALLSELECTED(Table[Current]); Table[Column1] ='Yes' || Table[Column2] ='Yes')

This should work. Then just divide 1 measure by the other to get the percentage.

Regarding the columns vs measure best practices on DAX says thst if you can calculate it making a measure don't use columns.

Columns add size to your model and decrease performance since you are adding values to your model, assuming you table as 100.000 rows adding one column is adding 100.000 additional data cells.

Measures are only calculated when they are called and also store on cache for next uses.

Regards

MFelix

**Did I answer your question? Mark my post as a solution!**

You are correct this ask for a table if want to apply any filters you should wrap your column in a ALLSELECTED if you want an overall result the use the ALL function.

In the case the measure would be something like this:

TotalValBranches = calculate (countrows(ALLSELECTED(Table[Current]); Table[Column1] ='Yes' || Table[Column2] ='Yes')

This should work. Then just divide 1 measure by the other to get the percentage.

Regarding the columns vs measure best practices on DAX says thst if you can calculate it making a measure don't use columns.

Columns add size to your model and decrease performance since you are adding values to your model, assuming you table as 100.000 rows adding one column is adding 100.000 additional data cells.

Measures are only calculated when they are called and also store on cache for next uses.

Regards

MFelix

Proud to be a Datanaut!

v-lili6-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2018
12:00 AM

hi, @BBIUser

Your formulas are M language in the Edit Queries.

For creating the calculate column by DAX

You could click Modeling ->New Column

Then use these formulae to create three column

TotalValBranches = IF([Column1] = "Yes" || [Column2] = " Yes", 1, 0)

TotalAttBranches = if (([Column1] = "Yes" || [Column2]= "Yes") && [Current]=0 && [Expected]=0, 1 , 0)

TotalChBranches = IF([Column3] = "Yes", 1 , 0)

Comparison

And then add a measure for TotalValBranches/TotalAttBranches by DIVIDE Function

TotalValBranches/TotalAttBranches = DIVIDE ( CALCULATE ( SUM ( 'Table'[TotalValBranches] ) ), CALCULATE ( SUM ( 'Table'[TotalAttBranches] ) ), 0 )

and drag it into card visual.

also, you could just create a measure like below:

Measure = DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Column1] = "Yes" || [Column2] = " Yes" ) ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( [Column1] = "Yes" || [Column2] = "Yes" ) && [Current] = 0 && [Expected] = 0 ) ), 0 )

Columns add size to your model and decrease performance since you are adding values to your model and they are static.

Measures are only calculated when they are called and also store on cache for next uses and they are dynamic.

here is my pbix, please try it.

Best Regards,

Lin

Community Support Team _ Lin

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

If this post

5 REPLIES 5

MFelix

Super User

Re: How to use this CASE statement to DAX or to a Custom Column?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018
01:01 PM

Hi @BBIUser,

In DAX you need to define the filter to make this calculations work correcly (in this case) in other you may need to use the Switch or calculate function.

Try the formulas below:

TotalValBranches = calculate (countrows(Table[Current] ; Table[Column1] ='Yes' || Table[Column2] ='Yes') TotalAttBranches = calculate (countrows(Table[Current] ; Table[Column1] ='Yes' || Table[Column2] ='Yes';Table[Current] = 0 ; Table[Expected] = 0 ) TotalChBranches = calculate (countrows(Table[Current] ; Table[Column3] ='Yes')

These 3 measures should give expected result.

Regards,

MFelix

Proud to be a Datanaut!

BBIUser

Member

Re: How to use this CASE statement to DAX or to a Custom Column?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-11-2018
11:53 AM

Appreciate your reply @MFelix.

Does the CALCULATE(COUNTROWS(.. DAX accept the Column name after the COUNTROWS? I was getting errors and the expression was expecting a Table Name.

I tried creating an 'Custom Columns' for all the CASE statements. Below is an example in order.

TotalValBranches

if ([Column1] = "Yes" or [Column2] = " Yes") then 1 else 0

TotalAttBranches

if (([Column1] = "Yes" or [Column2]= "Yes") and [Current]=0 and [Expected]=0) then 1 else 0

TotalChBranches

if (Column3 = "Yes") then 1 else 0

I am not confident creating a custom column is correct or not, but I am still having trouble defining DAX from these custom columns too.

My final result should show TotalValBranches/TotalAttBranches values that will be displayed in a card or multi-row card as (numerator and denominator). Finally the % from these Numtor and Denomtor.

Report Inappropriate Content

Message 3 of 6

MFelix

Super User

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-11-2018
02:16 PM

You are correct this ask for a table if want to apply any filters you should wrap your column in a ALLSELECTED if you want an overall result the use the ALL function.

In the case the measure would be something like this:

TotalValBranches = calculate (countrows(ALLSELECTED(Table[Current]); Table[Column1] ='Yes' || Table[Column2] ='Yes')

This should work. Then just divide 1 measure by the other to get the percentage.

Regarding the columns vs measure best practices on DAX says thst if you can calculate it making a measure don't use columns.

Columns add size to your model and decrease performance since you are adding values to your model, assuming you table as 100.000 rows adding one column is adding 100.000 additional data cells.

Measures are only calculated when they are called and also store on cache for next uses.

Regards

MFelix

Proud to be a Datanaut!

v-lili6-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2018
12:00 AM

hi, @BBIUser

Your formulas are M language in the Edit Queries.

For creating the calculate column by DAX

You could click Modeling ->New Column

Then use these formulae to create three column

TotalValBranches = IF([Column1] = "Yes" || [Column2] = " Yes", 1, 0)

TotalAttBranches = if (([Column1] = "Yes" || [Column2]= "Yes") && [Current]=0 && [Expected]=0, 1 , 0)

TotalChBranches = IF([Column3] = "Yes", 1 , 0)

Comparison

And then add a measure for TotalValBranches/TotalAttBranches by DIVIDE Function

TotalValBranches/TotalAttBranches = DIVIDE ( CALCULATE ( SUM ( 'Table'[TotalValBranches] ) ), CALCULATE ( SUM ( 'Table'[TotalAttBranches] ) ), 0 )

and drag it into card visual.

also, you could just create a measure like below:

Measure = DIVIDE ( CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', [Column1] = "Yes" || [Column2] = " Yes" ) ), CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', ( [Column1] = "Yes" || [Column2] = "Yes" ) && [Current] = 0 && [Expected] = 0 ) ), 0 )

Columns add size to your model and decrease performance since you are adding values to your model and they are static.

Measures are only calculated when they are called and also store on cache for next uses and they are dynamic.

here is my pbix, please try it.

Best Regards,

Lin

Community Support Team _ Lin

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

If this post

BBIUser

Member

Re: How to use this CASE statement to DAX or to a Custom Column?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-12-2018
06:23 PM