Robotically Assign a Class to Uncategorized Rows in Energy Question and DAX

The State of affairs

to report on seat possession.

Every seat within the workplace constructing should be assigned to an organisational unit (OU).

Constructing Efficient AI Brokers With out Over-Engineering

Many Firms Use AI. Few Know The right way to Construct an AI-Native Enterprise Knowledge Platform.

There’s a listing of all seats, and the proudly owning OU must be set for every seat. However some seats don’t have an proudly owning OU set.

Subsequently, they determined to assign all unassigned seats to the OU that owns essentially the most seats.

This should be completed by room and by ground.

Take a look at the next ground plan:

Determine 1 – Instance ground plan with unassigned seats (This isn’t an actual one. Simply an instance created by the Writer)

Take a look at the marked seats in room B.

As you’ll be able to discover, “OU 3” owns essentially the most seats in that room.

Subsequently, these seats should be assigned to that OU.

However when trying on the complete ground, “OU 5” personal essentially the most seats throughout all rooms.

If there’s a room on that ground the place all seats aren’t assigned, they should be assigned to “OU 5”.

These are the enterprise guidelines for assigning unassigned seats.

The information supply

All the info is saved in Excel information.

Every file has a date in the beginning of the filename.

For instance:

20260331_Seatlist.xlsx
20260430_Seatlift.xlsx
20260531_Seatlist.xlsx

There may be one file per thirty days that comprises all current seats within the constructing, together with their assignments for that month.

It’s because the assignments change over time and the person should be capable of see the variations.

However solely the most recent dataset (Excel file) must be used to assign the unassigned seats.

Do it in Energy Question

As you may know from my earlier articles, I goal to carry out knowledge transformations as early as doable within the load chain.

Subsequently, it was pure to begin doing the work in Energy Question.

What I wanted to do was the next steps for every row with out an assigned OU:

Discover the most recent file amongst all information loaded
Learn this file
Seek for the rows for the present room (The room within the present row)
Depend the variety of seats per OU in that room
Kind descending by the variety of seats
Maintain solely the primary row – the one with the OU that has the best variety of seats
Assign that OU to the present row

For this, I created an M-function [CheckMax_ForSeat].

The code for this operate is comprised of the next segments:

1. Discover the most recent file:

Supply = Folder.Information(SourceFolder & "Seatlists"),
#"Filtered Rows" = Desk.SelectRows(Supply, every Textual content.StartsWith([Name], "20")),
#"Sorted Rows" = Desk.Kind(#"Filtered Rows",{{"Title", Order.Descending}}),
#"Saved First Rows" = Desk.FirstN(#"Sorted Rows",1)

This code works, because the date is initially of the file identify, as talked about above.

2. Learn the file:

#"Added Customized" = Desk.AddColumn(#"Saved First Rows", "FullFilePath", every [Folder Path] & [Name], kind textual content),
#"Invoked Customized Perform" = Desk.AddColumn(#"Added Customized", "ReadSingleFile_Seatlist", every ReadSingleFile_Seatlist_ForSeat([FullFilePath])),
#"Eliminated Different Columns" = Desk.SelectColumns(#"Invoked Customized Perform",{"Title", "Date modified", "ReadSingleFile_Seatlist"}),
#"Expanded ReadSingleFile_Seatlist" = Desk.ExpandTableColumn(#"Eliminated Different Columns", "ReadSingleFile_Seatlist", {"OU-No.", "Room-No."}, {"OU-No.", "Room-No."}),
#"Modified Kind" = Desk.TransformColumnTypes(#"Expanded ReadSingleFile_Seatlist",{{"OU-No.", kind textual content}, {"Room-No.", kind textual content}})

As you’ll be able to see, I exploit a second M-Perform to learn the file: ReadSingleFile_Seatlist_ForSeat

This file is used to learn the present file.

It comprises the M-code to learn an Excel file and maintain solely the wanted columns.

You get the identical M-code if you import a single Excel file in Energy Question.

Because of this, I can’t present it right here.

3. Maintain solely the rows for the present room and get the OU with the best variety of seats:

#"Filter out Empty OE" = Desk.SelectRows(#"Modified Kind", every ([#"Room-No."] = RoomNo) and ([#"OU-No."] <> null)),
#"Grouped Rows" = Desk.Group(#"Filter out Empty OU", {"OU-No.", "Assigned_OU"}, {{"Seat_Count", every Desk.RowCount(_), Int64.Kind}}),
#"Sorted Seat_Count" = Desk.Kind(#"Grouped Rows",{{"Seat_Count", Order.Descending}, {"Assigned_OU", Order.Ascending}}),
#"Saved highest Seat Depend" = Desk.FirstN(#"Sorted Seat_Count",1)

I have to carry out these operations twice:

As soon as for the seats in the identical room
As soon as for the rooms on the identical ground

The result’s two columns, containing the OU to assign in line with the identical room or the ground.

If the room has no assigned seats, set the OU to the OU with the best variety of seats on your entire ground.

In any other case, take the OU with essentially the most assigned seats in the identical room.

It labored very effectively on my laptop computer.

However then…

This isn’t sensible

I encountered two main points:

As quickly as I switched the supply to a network-based folder, efficiency dropped drastically.

A SharePoint folder was the worst, adopted by a shared folder on a file server.

The issue was that the M-functions talked about above should be executed as soon as for every row within the dataset.

This resulted in round 1 GB of information being learn, whereas the full throughout the three accessible information is 300 KB.

The reason for the drop in efficiency wasn’t the quantity of information learn, because it labored effectively on my laptop computer. The explanation was community site visitors latency. Every spherical journey value time, which resulted in a considerable amount of time wanted to load the info,

By the way in which, the Energy BI file is barely 4 MB after loading the info and assigning the OUs to all seats.

It took round one hour to load the info from the shared folder – over two hours from SharePoint.

The opposite subject was much more extreme.

Whereas it labored in Energy BI Desktop, it didn’t work after publishing it to the Service.

The reason being that dynamic knowledge sources will not be allowed in Energy Question.

Right here are some hyperlinks about this subject.

I attempted the approaches talked about there, however they weren’t relevant as a result of the trail at all times adjustments between executions, because the newest file can change.

Subsequently, I used to be compelled to desert this method and rethink easy methods to clear up it.

Do it in DAX

Now it’s DAX’s flip.

I have to create two calculated columns:

Mark the latest dataset/file
Assign the OU to unassigned seats

The DAX expression to mark the latest file could be very easy:

IsNewestFile = 
    VAR LatestFileDate = 
            CALCULATE(MAX('Raumliste_HP'[FileDate])
                        ,REMOVEFILTERS('Roomlist')
                        )

RETURN
    IF( LatestFileDate = 'Raumliste_HP'[FileDate]
            ,TRUE()
            ,FALSE()
            )

Step one is to leverage context transition to get the date of the latest file.

To attain this, I extracted the primary 8 characters of the file identify (e.g., 20260731_Seatlist.xlsx) and saved them within the [FileDate] column. I did this in Energy Question.

Then, I in contrast the FileDate of the present row to that worth.

When it matches, I assign TRUE; if not, FALSE.

Subsequent, I added one other calculated column to assign the OU to every seat.

To develop this logic, I wrote a DAX question to simulate and check the outcomes for one room at a time.

First, I created an inventory of assigned OUs for one particular room from the latest dataset (The column names are in German, as I labored with a German dataset):

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    )
                                    
EVALUATE
    ListOfOU

That is the end result:

Determine 2 – The question and the results of all assigned OUs for one room (Determine by the Writer)

Subsequent, I exclude the rows with out an [Assigned_OU] (Column Rauminhaber_OE), and I rely the rows for the remaining rows:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                        ,"@RowNo", COUNTROWS(Raumliste_HP)
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                    )
                                    
EVALUATE
    ListOfOU

Right here you see the end result:

Determine 3 – That is the results of the question to rely the rows (seat) for every OU (Determine by the Writer)

The third step is to order the end result by the variety of rows in descending order:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                        SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                        ,Raumliste_HP[Rauminhaber_OE]
                                        ,"@RowNo", COUNTROWS(Raumliste_HP)
                                    )
                                    ,REMOVEFILTERS(Raumliste_HP)
                                    ,Raumliste_HP[Raum-Nr.] = RoomNr
                                    ,Raumliste_HP[IsNewestFile] = TRUE()
                                    ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                    )

EVALUATE
    ListOfOU
    ORDER BY [@RowNo] DESC

The results of this question is that this:

Determine 4 – End result after sorting the end result by the variety of rows (Determine by the Writer)

The final step is to get the primary row, a.ok.a. the OU with essentially the most assigned seats:

DEFINE
    VAR RoomNr = "HP 8D01"
    
    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNr
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )


EVALUATE
    TOPN( 1
        ,ListOfOU
            ,[@RowNo], DESC
            )

The result’s one row:

Determine 5 – The question and the end result to get the primary row (Determine by the Writer)

As you’ll be able to see, I can omit the ORDER BY because the parameters in line 82 have the identical impact.

However what occurs if a number of OUs have the identical variety of assigned seats in a single room?

The question above will return two rows, as TOPN() can not distinguish between them:

Determine 6 – Question leading to two rows with the identical Row-Depend (Determine by the Writer)

We will have essentially the most elaborate algorithm to reply this query, or we are able to add the OU as a sorting column to get one row:

DEFINE
    VAR RoomNr = "HP 8B01"
    
    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNr
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )


EVALUATE
    TOPN( 1
        ,ListOfOU
            ,[@RowNo], DESC
            ,[Rauminhaber_OE], ASC
            )

Now, we get just one row within the end result:

Determine 6 – Dealing with ties by including the column with the assigned_OU as a sorting column (Determine by the Writer)

Now I’ve the complete logic to assign an OU to every seat in a room.

That is the code of the calculated column:

Assigned OU_ByRoom =
    VAR RoomNo = 'Raumliste_HP'[Raum-Nr.]

    VAR ListOfOU = CALCULATETABLE(
                    SUMMARIZECOLUMNS(Raumliste_HP[Raum-Nr.]
                                    ,Raumliste_HP[Rauminhaber_OE]
                                    ,"@RowNo", COUNTROWS(Raumliste_HP)
                                )
                                ,REMOVEFILTERS(Raumliste_HP)
                                ,Raumliste_HP[Raum-Nr.] = RoomNo
                                ,Raumliste_HP[IsNewestFile] = TRUE()
                                ,NOT ISBLANK(Raumliste_HP[Rauminhaber_OE])
                                )
    VAR End result =
                TOPN( 1
                    ,ListOfOU
                        ,[@RowNo], DESC
                        ,[Rauminhaber_OE], ASC
                        )
RETURN
IF(NOT ISBLANK('Raumliste_HP'[Rauminhaber_OE])
    ,'Raumliste_HP'[Rauminhaber_OE]
    ,SUMMARIZE(End result
                ,[Rauminhaber_OE])
    )

Now I have to apply the identical logic to your entire ground.

I add a second calculated column utilizing nearly the identical logic, however this time for the ground fairly than the room.

Lastly, I have to assign the calculated OU task to the rows with out an OU task.

This may observe the identical logic as I carried out within the Energy Question answer.

I did it with a SWITCH() assertion:

Assigned seat OU =
    SWITCH(TRUE()
        ,ISBLANK('Raumliste_HP'[Rauminhaber_OE]) && ISBLANK('Raumliste_HP'[Assigned OU_ByRoom])
            ,'Raumliste_HP'[Assigned OU_ByFloor]
        ,ISBLANK('Raumliste_HP'[Rauminhaber_OE])
            ,'Raumliste_HP'[Assigned OU_ByRoom]
        ,'Raumliste_HP'[Rauminhaber_OE])

The ultimate end result seems to be like this:

Determine 7 – A number of rows with the ultimate choice. When the task by room wasn’t doable, the OU is assigned by ground (Determine by the Writer)

As you’ll be able to see, the OU is assigned by room when a price is current. If no task is feasible by room, the OU is assigned by ground.

Conclusion

It was an fascinating journey to construct this answer.

I adopted the precept of remodeling knowledge as early as doable, and the end result wasn’t viable.

Though it labored in Energy BI Desktop with native information.

For my part, it is a flaw in Energy BI: I can construct an answer in Energy BI and never obtain any warning or indication that it may not work throughout improvement or whereas publishing it to the cloud service.

That is the second time I skilled this.

The opposite state of affairs was once I mixed cloud knowledge with on-premises knowledge. It labored in PBI Desktop, however the knowledge couldn’t be loaded into the cloud service. The error message was that it was not allowed to load knowledge from completely different sources, although I set the Privateness stage to the identical (the cloud supply was an organization SharePoint On-line folder).

In each conditions, I used to be compelled to construct the answer within the knowledge mannequin and with DAX.

Within the case described right here, the DAX answer was much less advanced than the Energy Question answer. However this isn’t at all times the case.

Realizing the constraints of Energy Question within the cloud helps keep away from spending an excessive amount of time constructing an answer that should be refactored resulting from unsupported combos of information or efficiency points.

However this data comes over time.

Anyway, there is a matter when doing it in DAX in comparison with doing it in Energy Question: now, I’ve columns within the knowledge mannequin that include middleman outcomes.

I solved it by including the “_original” appendix to the columns to get replaced from the unique knowledge. As well as, I set these columns as hidden and added them to a Show folder named “Middleman columns/Do-not-use”. This fashion, I can be certain that they aren’t used.

This may be averted by making ready the info earlier. Then I can take away the middleman columns and cargo solely the columns with cleansed knowledge into the info mannequin.

As a conclusion, my realisation was that the “proper method” of doing one thing isn’t at all times the right method.

Don’t dogmatically maintain on to the “proper method”. Suppose critically and choose the right option to do one thing primarily based in your expertise.

However it’s the conventional strategy of studying to make a flawed choice. This occurs to all people.

Don’t be afraid of it.