DAX help, countrows by rox context

[Joerobert]

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.

[Joerobert]

Step 1					Step 2					Step 3
NPT RefNo	Well Name	Time Duration	RefNo01 (count)		RefNo1	Well Name	Time Duration	RefNo01 (count)		RefNo1	Well Name	Time Duration (sum)	RefNo01 (count)
1.0	Well #1	5	1		1.0	Well #1	5	1		1.0	Well #1	15	1
1.1	Well #1	5	2		1.0	Well #1	5	2		1.0	Well #2	5	2
1.2	Well #1	5	3		1.0	Well #1	5	3		2.0	Well #2	5	3
1.0	Well #2	5	4		1.0	Well #2	5	4		3.0	Well #2	5	4
2.0	Well #2	5	5		2.0	Well #2	5	5		1.0	Well #3	5	5
3.0	Well #2	5	6		3.0	Well #2	5	6		2.0	Well #3	10	6
1.0	Well #3	5	7		1.0	Well #3	5	7
2.0	Well #3	5	8		2.0	Well #3	5	8
2.1	Well #3	5	9		2.0	Well #3	5	9
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]    ) )

[Greg_Deckler]

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

[Joerobert]

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.

[Greg_Deckler]

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))