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: And that's the expected result I want to achieve: For now, I have created a calculated tabl...
Need Help
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