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.
@@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.
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.
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
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?
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.
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
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?
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.
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 ❤
Could you please give me solution, I have two tables charges and payment my requirement is payment comes month on month based on charge bill month. Suppose jan2024 charge billed $100 so $40 payment comes in jan2024 , $20 payment comes in feb2024, $15 comes in March 2024 and so on so. Bill ID is unique column in both table. How to calculate and show in matrix as Row Charge. Jan24. Feb24 mar24 Jan2024. $40. $20. $15 Feb2024. $60. $25 Please give me solution in pawar bi.
Thank you Alberto,
I like the unplugged videos because they show how you are solving the problems.
This is fantastic video to understand and see in actions some Dax formulas! Thanks as always!
I am always impressed by your innovational and brilliant ideas!
Very informative video and your way of explaining is very clean and simple to understand anyone .Thank you so much....
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.
Why did we use CountRows in the FilterExpression of FILTER function? Doesn't it return a scalar value?
I would also like to understand how that works.
Can you point me to the minute in the video, so I better understand your question? Thanks.
@@SQLBI Hi. It is just after 15:30.
@@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.
34:30 Caught my attention when said that this could be done in Storage Engine (SE)
Hi. How did you do that zoom in the video while cratinh dax measure matching skills and at 22:18 secs
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.
Great examples as always! you can get the file
You can find the link in the description.
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.
Have a look at "Master Your Data with Excel and Power BI" by Miguel Escobar and Ken Puls.
Hey Alberto,
In 10:45 video, can we use values (jobreqs[Skill ID]) instead of using summarize dax?
thanks.
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
Jobreq expands to skills, therefore you can SUMMARIZE by Skill, which in turn is used to filter the other bridge.
@@SQLBI Oh so there is an expanded table concept, thanks
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?
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.
@@SQLBI Thanks very much. This makes sense now, similar to use the ISEMPTY to check whether the nested FILTER returns an empty table.
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
Great catch. I did not check it, but it looks like you are right!
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?
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.
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 ❤
Could you please give me solution,
I have two tables charges and payment my requirement is payment comes month on month based on charge bill month.
Suppose jan2024 charge billed $100 so $40 payment comes in jan2024 , $20 payment comes in feb2024, $15 comes in March 2024 and so on so. Bill ID is unique column in both table. How to calculate and show in matrix as
Row
Charge. Jan24. Feb24 mar24
Jan2024. $40. $20. $15
Feb2024. $60. $25
Please give me solution in pawar bi.
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))