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