Differences between GENERATE and CROSSJOIN solving business scenarios - Unplugged #37

Explore the differences between GENERATE and CROSSJOIN while discovering different solutions to solve a business problem described during the Guy in a Cube Q&A Live of October 9, 2021 ( • REPLAY Power BI tips ... ).
The question was how to match employees with skills at a particular level to job requirements of skills at a particular level.
You can download the sample file used in the video from this page:
www.sqlbi.com/tv/differences-...
Read more about the "unplugged" format: www.sqlbi.com/blog/marco/2021...
#unplugged

[@mohammed333suliman]

Thank you Alberto,
I like the unplugged videos because they show how you are solving the problems.

[@pilarbaldominos2505]

This is fantastic video to understand and see in actions some Dax formulas! Thanks as always!

[@riyazmca]

Very informative video and your way of explaining is very clean and simple to understand anyone .Thank you so much....

[@yeeren8970]

I am always impressed by your innovational and brilliant ideas!

[@Milhouse77BS]

34:30 Caught my attention when said that this could be done in Storage Engine (SE)

[@mikej62]

Hi, awesome video! Question for you, given the same dataset what if the “Job Name” requirements is for multi-select? How would you approach it? Thanks in advance.

[@janwillemvanholst]

My solution:
_test2 =
VAR __Skill =
ADDCOLUMNS(
SUMMARIZE(JobReqs, Skills[Skills ID]),
"@req", CALCULATE(SUM(JobReqs[Level])),
"@avail", CALCULATE(SUM(EmployeeSkills[Level]))
)
VAR __match =
FILTER(
__Skill,
[@avail] >= [@req]
)
RETURN
DIVIDE( COUNTROWS(__match), COUNTROWS(__Skill))

[@umangdbz]

Why did we use CountRows in the FilterExpression of FILTER function? Doesn't it return a scalar value?

[@simonloughnane6683]

I would also like to understand how that works.

[@SQLBI]

Can you point me to the minute in the video, so I better understand your question? Thanks.

[@simonloughnane6683]

@@SQLBI Hi. It is just after 15:30.

[@simonloughnane6683]

@@SQLBI I think you have answered the point in your response to Tommy Hao below. You explain that COUNTROWS returning 0 would be considered FALSE and any other number as TRUE, so it is actually returning a Boolean when used inside FILTER.

[@AhmedAriem]

Great examples as always! you can get the file

[@SQLBI]

You can find the link in the description.

[@lucasshipsides1510]

it makes me feel better when i see Alberto stop dead in his tracks mid formula to stare into the distance and go "fuck, how am i going to do this". thank you for sharing as always ❤

[@naveen200848]

Hi. How did you do that zoom in the video while cratinh dax measure matching skills and at 22:18 secs

[@SQLBI]

Just using ZoomIt. It used to work with Windows 10 in live zoom. It stopped working with Windows 11, but I do hope a fix is coming soon.

[@stachulec]

considering that the levels are integers, wouldn't it be sensible to use something like: GENERATESERIES ( 1, MAX ( EmployeeSkills[Level] ), 1 ) instead of FILTER & ALLNOBLANKROW? I have a feeling that ALLNONBLANKROW has a gap in a scenario when no employee one would have skill on level 2, just 1&3

[@SQLBI]

Great catch. I did not check it, but it looks like you are right!

[@DanielWeikert]

Why does the first summarize solution work? You are using Jobreqs table which is not filtering skills table (which would filter Employee skills) If you use Skills table it would make sense but the many to one relationship from Jobreqs to Skills should not work. There is also no expanded table concept or anything applied. Could you please elaborate on that?
Thanks

[@SQLBI]

Jobreq expands to skills, therefore you can SUMMARIZE by Skill, which in turn is used to filter the other bridge.

[@DanielWeikert]

@@SQLBI Oh so there is an expanded table concept, thanks

[@noahhadro8213]

What book would your recommend for power query that is the equivalent to the "Definitive guide to DAX". In other words.... to learn everything about DAX = Difinitive guide to dax AND to learn everything about Power query = ?????. Specifically interested in the "M language" and "Power Query Performance." Thanks for all the great content.

[@Linger92]

Have a look at "Master Your Data with Excel and Power BI" by Miguel Escobar and Ken Puls.

[@martinbubenheimer6289]

I love learning from you! As discussed in the end, I'd be a bit worried about performance with larger datasets like 100000 employees, 3000 open positions, an average of 10 skills per employee and 5 levels that we distinguish. I'd address this with a calculated table that already has all matches of employees and jobs. What do you think?

[@SQLBI]

Yes, having a test dataset would be nice. Anyway, if performance become important, then the model would need to be different, mainly avoiding the M2M to rely on regular 1:M relationships.

[@tommyhao7699]

Thanks very much Alberto.The second parameter of FILTER is a Filter Expression that is a boolean (True/False) expression that is to be evaluated for each row of the table. In the video you used COUNTROWS as the second parameter for FILTER. Could you please advise how COUNTROWS works, because COUNTROWS cannot return a boolean value?

[@SQLBI]

That was actually a mistake, that still worked by chance.
COUNTROWS returns the number of rows: 0 or BLANK stand for FALSE, any other number stands for TRUE. Therefore, it works, even though adding a "> 0" would make it much better.

[@tommyhao7699]

@@SQLBI Thanks very much. This makes sense now, similar to use the ISEMPTY to check whether the nested FILTER returns an empty table.