Dynamic dashboard , performance measuring parameter

[nn92]

I have built a dynamic reporting dashboard in Power BI and I would need you help in order to add one more parameter.

I have a column that contains time values in minutes that represent time needed for processing of the request. What I was trying to make is the chart that will show me the percentage of requests that is treated under one minute (e.g. divide number of fields that are less than 1 with total number of fields).

Edit Just to add, my data structure is one column that just contains time values in minutes (e.g. 0.67, 0.70,1.2,1.6...) I just need to complete simple calculation but automatically I can build this, but by filtering and dividing manually. I was wondering if there is anyone here who knows which functions in DAX or any other way to make it automatic in Power BI? I tried quite a lot, but I cant find a solution for this one. Also, please note that the source of data is an SQL code attached to datawarehouse that is being updated on a daily basis (that is why I am looking for more permanent solution)

[v-xicai]

Hi @nn92 ,

I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

[nn92]

Hi,

Thanks for replying. 

My dataset has following format: 0.9 , 0.3 , 0.6 , 1.2 , 1.6 , 1.8 etc. 

So number of minutes. Each row is one number. I just want to function in DAX that will be able to tell me the percentage of these nubmers that are smaller than 1. I tried today with COUNTA COUNTX  and similar, but not succesfully. 

it doesn't accept measure = countx TableName(CollumnName) > 1 

somehow it seems that DAX doesnt work with >< operators. 

[Cmcmahan]

DAX does work with < and > operators.  However the expression to determine what you're counting and the table you're counting on are separate. What you're trying to do is to count the number of times your expression (TableName[ColumnName] < 1) is TRUE().  For this, you want to use COUNTAX:

COUNTAX(FILTER(Table, Table[Column] < 1), Table[Column])

Now we need to get that as a percentage instead of just a count.

PercentMeasure = COUNTAX(FILTER(Table, Table[Column] < 1), Table[Column]) / COUNTA(Table[Column])

[nn92]

Hi,

Thanks for the answer, almost there!

PercentMeasure = COUNTAX(FILTER(Table, Table[Column] < 1), Table[Column]) / COUNTA(Table[Column])

The problem is that your code returnes value 1. 

I guess that DAX is filtering the table, counting the number of rows and then dividing it with the number of rows of filtered table (same number of rows). 

Any info on this? I managed to find "quick and dirty" solution - introducing 3 measures -

Measure 1 = total number of rows

measurure 2 = number of rows that are less than 1

measure 3 = measure 2/measure 1

it works, but it is not the most estetic solution 😄 

[nn92]

PercentMeasure = COUNTAX(FILTER(Table, Table[Column] < 1), Table[Column]) / COUNTA(Table[Column])

[Cmcmahan]

That exact measure works with my data.  Exact same setup, just a different condition for the numerator. It even changes as I slice different categories of the data.  The [Column] < 1 condition is only applied to the one operation, not the rest of the expression. Are you sure that you don't have a page level filter or something that removes all values that are less than 1?  If you were able to create a smaller, redacted set of data that demonstrates the issue, and share that .pbix file, I can do more specific troubleshooting.

Ways to really force this depends on your situtaion.  Do you want the denominator of your % to always be the count of ALL rows in your data?  If so, you can use something like:

PercentMeasure = COUNTROWS(FILTER('Table', 'Table'[Column] < 1)) / COUNTROWS(ALL('Table'))

This works, but then will do things if you filter the table later by another dimension (say by request type) it will still only return the values given over the entire table, leading to very low percentages, but working as you intend.