How to apply filter to only part of a formula

[Cmcmahan]

I'm trying to create a graph that shows a % of devices used on any particular day.  A device was "used" on a given day when the total time it was used is greater than 1 hour.  I have start and end times in the raw data. I also have a device category that distinguishes what type of hardware it was.

My end goal is to have a graph that looks generally like this:

A stripped sample of my raw data looks like this:

I was able to group it into a table that looks like this, with [Used] being a boolean that determines whether or not to count that day in the overall percentage:

My issue right now is with blanks in my data.  If there wasn't a recorded session on a given day, then I have no data that day, so there's nothing in the aggregated table.  This leads to weirdness when I display the graph where instead of using a percentage of all devices, it shows a percentage of devices that have data for that day.  So in the example above, on Jan 1 it would say devices in category A has 50% usage while for Jan 2 it would say 100% usage.  

The Measure I'm using to calculate the percentage looks like this: 

UsagePercentage = CALCULATE(SUM('AggregateTable'[Used])/DISTINCTCOUNT(DeviceList[DevID]))

I understand what is happening, but I'm not sure how to fix it without going through and manually adding blanks to my data (which is ~19k rows right now) so that each device would have an entry for each day. In addition, I would be worried about contaminating some of my other measures by adding in dummy data. 

I know that what I want is for the Percentage calculation to respect any filters I have in place (date/device category/etc) but to ignore date filters when counting devices in the DeviceList table. So where do I start looking to fix this?  

[v-lili6-msft]

hi, @Cmcmahan 

For If there wasn't a recorded session on a given day, then I have no data that day, so there's nothing in the aggregated table.  This leads to weirdness when I display the graph where instead of using a percentage of all devices, it shows a percentage of devices that have data for that day.  You may try to use “all” functions in your measure:

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

If not your case, please share more sample data and different expected output.

Best Regards,

Lin

[Cmcmahan]

Ah, I don't think that's exactly the answer I need, but it does get me much closer.  Instead of using the / symbol to divide, I can use the DIVIDE(A,B) to set different filters for both.

My original issue was that all filters were applying to the numerator and denominator. For this to work, I want all filters except the date filter to apply to the denominator.  

For example, if I had 20 devices of type A, some amount of them were red, and some were blue, I want the ability to determine what percentage of devices fit one description (e.g. type A devices that are red that were used on 1/1/2019). I can find this number easily.  The problem is finding the denominator for that percentage (which in this example would be the total number of type A devices that are red).   

ALLEXCEPT seems to do the opposite of what I want, which is removes all filters except the ones I list as parameters.  Is there a way to only remove one filter at a time?  In this case, removing only the date filter and keeping any other attribute filters?

I will create some more sample data and update with that shortly.

[Cmcmahan · Accepted solution]

I was able to solve the problem.  Using info found here: https://www.wiseowl.co.uk/blog/s2467/remove-filter.htm

It looks like my end calculation will look something like this:

UsagePercentage = DIVIDE( SUM('AggregateTable'[Used]), CALCULATE(DISTINCTCOUNT(DeviceList[ID]), ALL('Table'[Date]) ) )

I wasn't aware you could send an optional parameter into ALL to remove only one filter, and setting it up as two seperate parameters allowed me to remove filters on one half of the equation instead of the whole thing.