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

Understanding column in DAX function

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 numberbudget_timeplan_timeactual_hoursdescriptionsectionsection_typerig_nameproject_name
119,513,514Move to locationMove to LocationMove/Mob/DemobBig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
214913,5Anchor handling 2x vesselsMove to LocationMove/Mob/DemobBig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
3121110,75Space out riser joints and prepare for landing BOPPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
410911,75Land and latch BOP. Perform connector test. Meanwhile: Install UDF and WLR.Permanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
564,52,75Cross circulate with MEG/SW, Ventilate HXT and annulus. Pressure test annulus. Permanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
63224,75RIH with MRT and latch onto ITC. Meanwhile install WLRPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
71,513,25Pressure test BOP on ITC runPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
842,54POOH with MRT / ITCPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
932,52,5RIH with MSPT v2 to release TH plugPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
101,512,25Pressure test LPRPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
11461146Release TH plug. Kill oil zone with closed GLV. POOH with plug. Note: added 32 hrs budget / PW ref change logPermanent P&A (DP) w/ RIGP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
1243,53,75M/U punch and cut BHAPunchingP&ABig O'l oneKRAKEN - EN 45/8-M-15 CY1H,DY1H,DY2H,DY3H - P&A, Drilling, Completion P&A, DRILLING, COMPLETION
1313119,25 RIH to punch above and below prod, packerPunchingP&ABig O'l oneKRAKEN - 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?

 

1 ACCEPTED SOLUTION
AilleryO
Solution Sage
Solution Sage

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 🙂

View solution in original post

4 REPLIES 4
AilleryO
Solution Sage
Solution Sage

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 🙂

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
Community Champion
Community Champion

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

 

 

 

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 )

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors