Understanding column in DAX function

[beltalowda]

Hi all, 

I have some problem understanding how powerBI handles columns 

I have a large dataset composing of multiple excel files. Each file describing one project. Each file is something like this.

task number	budget_time	plan_time	actual_hours	description	section	section_type	rig_name	project_name
1	19,5	13,5	14	Move to location	Move to Location	Move/Mob/Demob	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
2	14	9	13,5	Anchor handling 2x vessels	Move to Location	Move/Mob/Demob	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
3	12	11	10,75	Space out riser joints and prepare for landing BOP	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
4	10	9	11,75	Land and latch BOP. Perform connector test. Meanwhile: Install UDF and WLR.	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
5	6	4,5	2,75	Cross circulate with MEG/SW, Ventilate HXT and annulus. Pressure test annulus.	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
6	3	2	24,75	RIH with MRT and latch onto ITC. Meanwhile install WLR	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
7	1,5	1	3,25	Pressure test BOP on ITC run	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
8	4	2,5	4	POOH with MRT / ITC	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
9	3	2,5	2,5	RIH with MSPT v2 to release TH plug	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
10	1,5	1	2,25	Pressure test LPR	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
11	46	11	46	Release TH plug. Kill oil zone with closed GLV. POOH with plug. Note: added 32 hrs budget / PW ref change log	Permanent P&A (DP) w/ RIG	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
12	4	3,5	3,75	M/U punch and cut BHA	Punching	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
13	13	11	9,25	RIH to punch above and below prod, packer	Punching	P&A	Big O'l one	KRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION

Each file is much larger with alot more coloumn, but this is how its structured. 

I want to have a shorter project name, and I am able to do this with a new column 

ProjectShortColoumn = 
MID(
    Project[project_name],FIND("-",Project[project_name], FIND("/",Project[project_name], ,1) 
    ,1)+1 ,5)

This return M-15 and thats perfect.

BUT:

A more elegant solution would be to achive the same thing with DAX, but when I try to make a measure with the same structure it tells me that it cant find a single value for "project_name" in table Project. 

Why does this not work, and how would I go by to produce the same result as with DAX?

[AilleryO · Accepted solution]

Hi,

A calculated column in DAX, is a calculation made row by row within your table and using the Row context of your table (granularity of one row). Knowing this, it is useless (in a calculated column) to specify the value you are using in the calculation, it will be the value of the current calculated row.

(In theory because using DAX functions you can change the context, but that is another thing).

Whereas when you create a measure there is no calculation made, it will be made directly as you put your measure in a visual (table or chart). So the result will rely on the visual, if you make a table with year you have yearly results, if you make a table with catagory you have results by categories , or by month, project name...

So when you create a measure, as the measure might have to agregate some values (rows), you need to specify how this agregation will be processed (SUM, MAX, MIN, SELECTEDVALUE for the value on the row of your visual...).

Another way of saying it would be, that measure are taking into account the whole column so you need to specify which value you want in the column.

Hope it makes things more clear, and for the formula thanks to @tamerj1 🙂

[beltalowda]

Hi,
Thanks all for your replies.
This post have made me think alot on how I structure my reports, and I believe I am a little bit closer to understanding when to use measures and when a new coulumn is appropriate. 

For the solution, the concatenatex function did give a calulateble solution, but for some edge cases it produced alot of duplicates. I did not spend to much time understanding why that happen and just went ahead and made a new coulumn with :

ProjectShortColoumn = 
MID(
    Project[project_name],FIND("-",Project[project_name], FIND("/",Project[project_name], ,1) 
    ,1)+1 ,5)

This is working flawlessly, so I understand now that there is no point in making a more complex calculation when a simple coloumn will be sufficent!

Thank you for all your insight !

[tamerj1]

Hi @beltalowda 
First of all this is most propobly need to be a column because otherwise you cannot use it to slice and filter your data. 
If you just want to disply the project name in the report as a measure then this can only happen when the filter context of the cell in your table or matrix visual contains only one project name. To do that you can use

ProjectShortMeasure =
IF (
    HASONEVALUE ( Project[project_name] ),
    CONCATENATEX (
        Project,
        MID (
            Project[project_name],
            FIND ( "-", Project[project_name], FIND ( "/", Project[project_name],, 1 ), 1 ) + 1,
            5
        )
    )
)

[AlexisOlson]

If it's just one value, then you don't really need CONCATENATEX.

ProjectShortMeasure =
VAR _Name = SELECTEDVALUE ( Project[project_name] )
RETURN
    MID ( _Name, FIND ( "-", _Name, FIND ( "/", _Name,, 1 ), 1 ) + 1, 5 )