Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Virtual table with crossjoin

Hello all!

I've been struggling with this one:

 

Below is an example of the tables I have:

Tables

And that's the expected result I want to achieve:

Expected calculated table

For now, I have created a calculated table with the following DAX:

 

Table = 
CROSSJOIN(
VALUES(Employees Table[Employee_ID]), 
VALUES(PPE Table[PPE])
)

 

With the above, I have a table with all the Employee_IDs and all the PPEs. Also, I was able to create calculated columns with another DAX expressions to lookup for Employee_Name, Function_Area and the Last Delivery Date. But yet I can't create the column that tells me if the PPE is mandatory based on that Function_Area, as per my example above.

 

The goal is to display which employees have their PPEs ok -- meaning they have what they need and are mandatory for 'em -- and the ones who don't.

 

Thanks in advance.



Need Help

1 Reply

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    If you have this table as described:

     

    Table1 = 
CROSSJOIN(
VALUES(Employees Table[Employee_ID]), 
VALUES(PPE Table[PPE])
)

     

     you can add a column specifying whether the equipment is compulsory for that function:

    Table2 =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( EmployeesTable[Employee_ID] ), VALUES ( PPETable[PPE] ) ),
    "IsCompulsory",
        VAR currentID_ = EmployeesTable[Employee_ID]
        VAR currentfunction_ =
            CALCULATE ( DISTINCT ( EmployeesTable[Function_Area] ) )
        RETURN
            PPETable[PPE]
                IN CALCULATETABLE (
                    DISTINCT ( PPETable[PPE] ),
                    PPETable[Function_Area] = currentfunction_,
                    ALL ( PPETable[PPE] )
                )
)

     

    Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers