cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Joerobert Regular Visitor
Regular Visitor

DAX help, countrows by rox context

Hello,

 

I am currently fighting through a DAX problem and cannot seem to come up with a solution, see below screenshot as a reference. I have a table visualization, shown in step 1, where i'm displaying a list of NPT events for a list of wells. The problem here is that I have sub events where the events were captured as 1.1, 1.2 and etc.. I do not want this and want to aggregate the total time associated with each event. As a solution, I created a calculated colume "RefNo1" that allows me to change the NPT RefNo to a whole number and aggreate the time duration (see step2 & 3). I now want to apply a count function to this column (RefNo1) so that I can visualize the total number of event for a group of wells and capture this on a smart card & tooltips. I created a measure to perform this calculation and am calling it "1RefNoCount".

The problem I am running into now is that all of the expressions i have come up with revert back to the original row context and is including the sub-events into the count. So for example, when I know that I have a total number of 6 NPT events for a group of wells, my expression is giving me 9 events.I am trying to put together a DAX expression that allows me to create a table based off well name, and then apply either a row count or distinct count of "RefNo1" for each well, then iterate for the entire group of wells.

The below expression for "1RefNoCount" is where I gave up and keep getting an error message "cannot convert value ## of type text to type true/false".

 

Your help is greatly appreciated, please let me know if you have any questions.

 

2018-04-21_9-50-05.jpg

4 REPLIES 4
Super User IV
Super User IV

Re: DAX help, countrows by rox context

Can you post that information as something that can be copied and pasted? Otherwise, we have to retype everything. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Joerobert Regular Visitor
Regular Visitor

Re: DAX help, countrows by rox context

Step 1    Step 2    Step 3   
NPT RefNoWell NameTime DurationRefNo01 (count) RefNo1Well NameTime DurationRefNo01 (count) RefNo1Well NameTime Duration (sum)RefNo01 (count)
1.0Well #151 1.0Well #151 1.0Well #1151
1.1Well #152 1.0Well #152 1.0Well #252
1.2Well #153 1.0Well #153 2.0Well #253
1.0Well #254 1.0Well #254 3.0Well #254
2.0Well #255 2.0Well #255 1.0Well #355
3.0Well #256 3.0Well #256 2.0Well #3106
1.0Well #357 1.0Well #357     
2.0Well #358 2.0Well #358     
2.1Well #359 2.0Well #359     
              
Card   Card   Card  
9   9   9  
1RefNocount   1RefNocount   1RefNocount  
              
calculated column            
REFNO1 =    if(          iserror(rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),-1)),        00,          rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),0.1)    )  
              
Measure             
1RefNoCount = CALCULATE(     DISTINCTCOUNT(wvJOBINTERVALPROBLEM[REFNO1]),    FILTER(        wvWELLHEADER_wvJOB,          wvWELLHEADER_wvJOB[WELLNAME]    ) )  
Joerobert Regular Visitor
Regular Visitor

Re: DAX help, countrows by rox context

Thanks Greg for that share, i will be sure to follow this from now on. please let me know if the repost still needs to be adjusted.  i will also look at other post for examples.

Highlighted
Super User IV
Super User IV

Re: DAX help, countrows by rox context

I'm having a tough time following exactly what you are trying to do here. But, if you use the first table and you just want the count without the sub-events, make sure that the first column is Text and not numeric and then you can use this:

 

Event Count = 
VAR tmpTable = ALL(Wells)
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"Filter",VALUE(RIGHT([NPT RefNo],1)))
RETURN COUNTROWS(FILTER(tmpTable1,[Filter]=0))

I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors