- Microsoft Power BI Community
- Welcome to the Community!
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish Community
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

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
- Re: IF(HASONEFILTER()) with a filter selection?

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

IF(HASONEFILTER()) with a filter selection?

04-16-2017
08:24 PM

So I'm trying to convert this logic to DAX. I have three different formulas that I use to get the utilization rate of a person that depends on their position. If:

1. Contractor

Utilization (C) = (Days Worked + OOF Days) / (Days Worked + OOF Days)

2. Other

Utilization (O) = (Days Worked + Travelling Days + OOF Days) / (Days Worked + Travelling Days + OOF Days)

* Essentially for Contractor/Other, if the sum of all required values > 0, 100%, but I have to use the formula and not the IF-ELSE

3. Regular Employees (General Formula)

Utilization (R) = (Days Worked + Travelling Days + OOF Days) / Possible Working Days

Basically, it should use the general formula if only the Regular Employees filter is selected, or if all filters are selected (ie: fallback). Is it possible to use HASONEFILTER with an "equals to" value? Like:

HASONEFILTER([Type] = "Contractor")

I'm trying this, but I'm not getting the correct value. If I have all three values selected, it just gets the formula for the Other and not the general formula.

Utilization Rate = IF(CONTAINS(test, [Type], "Other"), CALCULATE( DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]) ) ) , IF(CONTAINS(test, [Type], "Subcontractor"), CALCULATE( DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]), SUM([Days Worked]) + SUM(test[OOF Days]) ) ) , CALCULATE( DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM(test[Possible Workdays Per Month]) ) ) ) )

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-20-2017
02:40 AM

Hi @olimilo,

You can try to use below formuals:

Utilization Rate(Calculate Column) = SWITCH(Sheet3[Type], "O", DIVIDE( [Days Worked] + Sheet3[OOF Days]+ Sheet3[Travel Days], [Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days] ), "C", DIVIDE( [Days Worked] + Sheet3[OOF Days], [Days Worked] + Sheet3[OOF Days] ), "R", DIVIDE( Sheet3[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days], Sheet3[Possible Working Days] ) ) Utilization Rate(Measure) = var currtype=if(HASONEVALUE(Sheet3[Type]),VALUES(Sheet3[Type]),BLANK()) var currType2=LASTNONBLANK(Sheet3[Type],[Type]) return SWITCH(currtype, "O", DIVIDE( SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]), SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]) ), "C", DIVIDE( SUM([Days Worked]) + SUM(Sheet3[OOF Days]), SUM([Days Worked]) + SUM(Sheet3[OOF Days]) ), "R", DIVIDE( SUM(Sheet3[Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]), SUM(Sheet3[Possible Working Days]) ) )

>>Is there something I can use to replace the MAX() as the switch case for this?

You can use lastnonblank or isonevalue to get the current row value in measure.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin

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

If this post

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2017
12:06 AM

Hi @olimilo,

*>>Basically, it should use the general formula if only the Regular Employees filter is selected, or if all filters are selected (ie: fallback). Is it possible to use HASONEFILTER with an "equals to" value? Like:*

You can try to use below measure which used to get the select item from slicer:

Selected Item=IF(HASONEVALUE(Table[Column]),VALUES(Table[Column]),BLANK)

*>>I'm trying this, but I'm not getting the correct value. If I have all three values selected, it just gets the formula for the Other and not the general formula.*

For your requirement, you can try to use switch function with calculate column:

Calculate column: Utilization Rate = SWITCH(test[Type], "Other", DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]) ), "Subcontractor", DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]), SUM([Days Worked]) + SUM(test[OOF Days]) ), DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM(test[Possible Workdays Per Month]) ) ) Measure: Utilization Rate = SWITCH(MAX(test[Type]), "Other", DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]) ), "Subcontractor", DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]), SUM([Days Worked]) + SUM(test[OOF Days]) ), DIVIDE( SUM([Days Worked]) + SUM(test[OOF Days]) + SUM(test[Travelling Days]), SUM(test[Possible Workdays Per Month]) ) )

If above not help, please share some sample data to test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin

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

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2017
06:43 PM

Hi @v-shex-msft

Thanks for the solution. I have since reordered my data and with the given measure, it only gets the first case (just the Other) if I have all three types (Regular, Contractor, Other) selected and does not fall to the fall back formula (ie: the else case). Is there something I can use to replace the MAX() as the switch case for this?

Edit: I just checked, the MAX() only gets the last entry when you sort the 3 types, so it gets Subcontractor (since it goes last when you sort the 3 types alphabetically).

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-20-2017
02:40 AM

Hi @olimilo,

You can try to use below formuals:

Utilization Rate(Calculate Column) = SWITCH(Sheet3[Type], "O", DIVIDE( [Days Worked] + Sheet3[OOF Days]+ Sheet3[Travel Days], [Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days] ), "C", DIVIDE( [Days Worked] + Sheet3[OOF Days], [Days Worked] + Sheet3[OOF Days] ), "R", DIVIDE( Sheet3[Days Worked] + Sheet3[OOF Days] + Sheet3[Travel Days], Sheet3[Possible Working Days] ) ) Utilization Rate(Measure) = var currtype=if(HASONEVALUE(Sheet3[Type]),VALUES(Sheet3[Type]),BLANK()) var currType2=LASTNONBLANK(Sheet3[Type],[Type]) return SWITCH(currtype, "O", DIVIDE( SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]), SUM([Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]) ), "C", DIVIDE( SUM([Days Worked]) + SUM(Sheet3[OOF Days]), SUM([Days Worked]) + SUM(Sheet3[OOF Days]) ), "R", DIVIDE( SUM(Sheet3[Days Worked]) + SUM(Sheet3[OOF Days]) + SUM(Sheet3[Travel Days]), SUM(Sheet3[Possible Working Days]) ) )

>>Is there something I can use to replace the MAX() as the switch case for this?

You can use lastnonblank or isonevalue to get the current row value in measure.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin

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

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

04-18-2017
03:11 AM

Hi Xiaoxin!

Sample data in here:

Country | Type | Possible Working Days | Days Worked | OOF Days | Travel Days | UT Rate |

USA | R | 75 | 48 | 10 | 3 | 81% |

USA | C | 9 | 6 | 3 | 1 | 100% |

Canada | C | 2 | 2 | 0 | 0 | 100% |

Mexico | R | 85 | 38 | 7 | 29 | 87% |

Mexico | C | 35 | 32 | 3 | 0 | 100% |

Mexico | O | 5 | 5 | 0 | 0 | 100% |

Brazil | R | 64 | 37 | 8 | 4 | 77% |

Brazil | C | 13 | 13 | 0 | 2 | 100% |

El Salvador | C | 2 | 2 | 0 | 0 | 100% |

Haiti | C | 1 | 1 | 0 | 1 | 100% |

Peru | C | 1 | 1 | 0 | 1 | 100% |

The formula for the utilization varies according to type (R, C, O). Also, why was there a MAX() on the measure? Does that mean we're getting all three types in the filter?

Announcements

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Featured Topics

Top Solution Authors

User | Count |
---|---|

107 | |

74 | |

66 | |

49 | |

48 |

Top Kudoed Authors

User | Count |
---|---|

164 | |

87 | |

77 | |

70 | |

67 |