DAX help, countrows by rox context

Question

Hello,&nbsp;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 &amp; 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 &amp; tooltips.&nbsp;I created a measure to perform this calculation and am calling it "1RefNoCount".The problem I am running into&nbsp;now is&nbsp;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".&nbsp;Your help is greatly appreciated, please let me know if you have any questions.&nbsp;

greg_deckler · Answer

Can you post that information as something that can be copied and pasted? Otherwise, we have to retype everything.&nbsp;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 · Answer

Step 1&nbsp;&nbsp;&nbsp;&nbsp;Step 2&nbsp;&nbsp;&nbsp;&nbsp;Step 3&nbsp;&nbsp;&nbsp;NPT RefNoWell NameTime DurationRefNo01 (count)&nbsp;RefNo1Well NameTime DurationRefNo01 (count)&nbsp;RefNo1Well NameTime Duration (sum)RefNo01 (count)1.0Well #151&nbsp;1.0Well #151&nbsp;1.0Well #11511.1Well #152&nbsp;1.0Well #152&nbsp;1.0Well #2521.2Well #153&nbsp;1.0Well #153&nbsp;2.0Well #2531.0Well #254&nbsp;1.0Well #254&nbsp;3.0Well #2542.0Well #255&nbsp;2.0Well #255&nbsp;1.0Well #3553.0Well #256&nbsp;3.0Well #256&nbsp;2.0Well #31061.0Well #357&nbsp;1.0Well #357&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.0Well #358&nbsp;2.0Well #358&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2.1Well #359&nbsp;2.0Well #359&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Card&nbsp;&nbsp;&nbsp;Card&nbsp;&nbsp;&nbsp;Card&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;&nbsp;9&nbsp;&nbsp;1RefNocount&nbsp;&nbsp;&nbsp;1RefNocount&nbsp;&nbsp;&nbsp;1RefNocount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;calculated column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;REFNO1 = &nbsp;&nbsp; if( &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; iserror(rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),-1)), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 00, &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; rounddown(value(wvJOBINTERVALPROBLEM[REFNO]),0.1) &nbsp;&nbsp; )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Measure&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1RefNoCount = CALCULATE( &nbsp; &nbsp; DISTINCTCOUNT(wvJOBINTERVALPROBLEM[REFNO1]), &nbsp;&nbsp; FILTER( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; wvWELLHEADER_wvJOB, &nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; wvWELLHEADER_wvJOB[WELLNAME] &nbsp;&nbsp; ) )&nbsp;&nbsp;

joerobert · Answer

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.&nbsp; i will also look at other post for examples.

greg_deckler · Answer

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:&nbsp;Event Count = 
VAR tmpTable = ALL(Wells)
VAR tmpTable1 = ADDCOLUMNS(tmpTable,"Filter",VALUE(RIGHT([NPT RefNo],1)))
RETURN COUNTROWS(FILTER(tmpTable1,[Filter]=0))

Forum Discussion

DAX help, countrows by rox context

4 Replies

Related content

COUNTROWS

Context Filter Help

CountRows help

Countrows help

Countrows DAX help

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