In this writing, I want to share one of my ways how I make learning DAX authoring more enjoyable.
A great way I've found to start and to make DAX learning fun is by visiting Power BI forums - Microsoft Fabric Community. I explore the challenges people face, learn about the problems they encounter, and discover how others offer solutions.
Just recently, I came across a question about restructuring a table in DAX.
The straightforward and accurate solution is to reshape it in the Power Query editor as shown below, or even better, initiate the transformation upstream. For example, collaborate with the data warehouse team to restructure it. This significantly simplifies your work when it comes to data modeling, DAX authoring, and crafting visualizations in Power BI. Indeed, these are the most efficient and effective methods suggested by Power BI enthusiasts.
The final table structure, as depicted below, is how I envision it for use in Power BI.
Nevertheless, when confronted with inquiries about situations where no transformations are applied, and the unaltered table is loaded directly into Power BI, as illustrated below, I don't just suggest back transforming it in the Power Query editor or further upstream. Despite knowing that it's not the ideal approach to data modeling, I embrace the challenge and attempt to resolve it through DAX authoring.
This represents one of many ways how I start to get fun in learning writing DAX.
The following process outlines how I initiate and conclude.
Step 1
When replacing spaces in the [Option] column with "|," it closely resembles what I typically use as the first argument in PATHITEM DAX functions.
Step 2
I employ the PATHITEM DAX function and prepare to reshape the table according to my preferences.
Step 3
However, I've noticed that the [Option] column has a maximum of 26 characters per row. Manually creating additional columns and hardcoding numbers from 1 to 26 becomes challenging, especially considering the uncertainty of the [Option] column containing more than 100 characters in the near future.
To tackle this, I've opted for a more dynamic approach. I use the GENERATE DAX function in conjunction with the PATHITEM DAX function. By generating a series of numbers from 1 to "maximum space count + 1" using the GENERATESERIES DAX function to create a virtual table within the formula, I avoid hardcoding any numbers, providing flexibility to adapt to changing data scenarios.
The outcome displayed in the DAX Query View above corresponds to the following query I have written.
DEFINE
VAR _t = ADDCOLUMNS(
'Data',
"@spacecount", LEN('Data'[Option]) - LEN(SUBSTITUTE(
'Data'[Option],
" ",
""
)) + 1
)
VAR _maxspacecount = MAXX(
_t,
[@spacecount]
)
VAR _table = ADDCOLUMNS(
'Data',
"@replace", SUBSTITUTE(
'Data'[Option],
" ",
"|"
)
)
VAR _generateseries = SELECTCOLUMNS(
GENERATESERIES(
1,
_maxspacecount,
1
),
"@number",
[Value]
)
VAR _generatetable = GENERATE(
_generateseries,
ADDCOLUMNS(
_table,
"@pathitem", PATHITEM(
[@replace],
[@number]
)
)
)
EVALUATE
FILTER(
SUMMARIZE(
_generatetable,
'Data'[Date],
[@pathitem]
),
[@pathitem] <> BLANK()
)
ORDER BY Data[Date], [@pathitem]When attempting to craft a new table in Power BI, write it as follows.
Expected result table =
VAR _t =
ADDCOLUMNS (
'Data',
"@spacecount",
LEN ( 'Data'[Option] ) - LEN ( SUBSTITUTE ( 'Data'[Option], " ", "" ) ) + 1
)
VAR _maxspacecount =
MAXX ( _t, [@spacecount] )
VAR _table =
ADDCOLUMNS ( 'Data', "@replace", SUBSTITUTE ( 'Data'[Option], " ", "|" ) )
VAR _generateseries =
SELECTCOLUMNS ( GENERATESERIES ( 1, _maxspacecount, 1 ), "@number", [Value] )
VAR _generatetable =
GENERATE (
_generateseries,
ADDCOLUMNS ( _table, "@pathitem", PATHITEM ( [@replace], [@number] ) )
)
RETURN
FILTER (
SUMMARIZE ( _generatetable, 'Data'[Date], [@pathitem] ),
[@pathitem] <> BLANK ()
)In wrapping up, it's evident that restructuring the table in the described manner isn't the optimal practice, a fact known to many.
However, what I truly wanted to convey is my unique approach to problem-solving when practicing and learning. I find joy in exploring diverse methods, showcasing my thought process.
Learning the multitude of ways to tackle challenges adds an element of fun and enjoyment to the journey of authoring DAX.
I hope this helps having more fun in learning DAX. Additionally, I've included the sample PBIX file as an attachment below.
Comments