create new measure based on multiple criteria and uniqueID

[Anonymous]

Hi all,

Really sorry, complete newbie in DAX 🙂

I need to have a measure that count the IDs that have skills / subskills.

To have a skill it is necessary to have all the traings corresponding to that skill, and the number of trainings can vary depending on the skill (example: for power bi it is necessary to have 3 trainings: training 1, training 2 and traing 3, while for sharepoint is required to have 2 trainings: training 7 ans training 8). In this example, ID 1 has the power bi skill because it has passed the 3 trainings but ID 2 hasn't the skill because it only has training 2 and 3.

In each skill we can have sub skills and just have a sub skill (corresponding to the skill) to have the skill.

In case of lot , to have this skill it is necessary to have the traings 20, 21 22 and 23, or else to have the trainings 20, 21, 22 and 24, so in this case IDs 6 and 7 have this skill.

I remember that I want only counting the IDs that have skills / sub skills and to have this it is necessary that the IDs have the corresponding trainings.

This is the table that I have for make the measure.

Thank you,

[Jimmy801]

Hello @Anonymous 

i suppose your table shows employees (ID) with differenct trainings. And now you want to count how many skills has somebody.

If this is true, you would need a matrix where you build up all the possibilities to get a skill and afterwards you could do some comparison (but you need to find a way to connect both tables and to implement a column to assign the percentage of an skill gotten with every training)

Jimmy

[Anonymous]

I use this measure but this isn't result and doens´t give me the correct number of skills per ID.

Can you help me please?

Thank you

[Jimmy801]

Hello @Anonymous 

Unfortunatly such calculations are not that easy to make. I've tried an approach with a virtual table, apply a sumx to it and afterwards filter it accordingly. Here the new measure

SkillPerID = var reportid = SELECTEDVALUE(Report[ID]) return
COUNTROWS(
    Filter(
    ADDCOLUMNS(
        SUMMARIZE(
            Report;
            Report[ID];
            Report[Skill];
            Report[Sub-skill]
        );
        "Sum";
        CALCULATE(
            SUMX(Report;[% training]);
            Filter(
                Report;
                [ID]=EARLIER(Report[ID]) && [Skill]=EARLIER(Report[Skill])&&[Sub-skill]=earlier(Report[Sub-skill])
            )));Report[ID]=reportid && [Sum]>=0,99))

my outcome is like this (didn't apply your whole table)

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thank you so much Jimmy, but I need a table when I have name of skills and the number total of each skill, and these skills are done by the IDs. I need too the total number of that skills. That measure do the number of skills per ID and I need number of skills per skill please and a total of this. Do you understand?

Thank you so much, can you help me do this measure please?

[Jimmy801]

Hello @Anonymous 

so create a matrix like this, or what is missing here?

Jimmy

[Anonymous]

Hi Jimmy,

This table isn't what I want. I want a table when I have two columns: All of skills in first column and the total number of skills with final total in another column.

Thank you Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

that was never specificated. This will be my last attempt to propose a solution. 

Changed the measure to 

SkillCount = var reportskill = SELECTEDVALUE(Report[Skill]) 
var reportsubskill = SELECTEDVALUE(Report[Sub-skill])return
COUNTROWS(
    Filter(
    ADDCOLUMNS(
        SUMMARIZE(
            Report;
            Report[ID];
            Report[Skill];
            Report[Sub-skill]
        );
        "Sum";
        CALCULATE(
            SUMX(Report;[% training]);
            Filter(
                Report;
                [ID]=EARLIER(Report[ID]) && [Skill]=EARLIER(Report[Skill])&&[Sub-skill]=earlier(Report[Sub-skill])
            )));Report[Skill]=reportskill && Report[Sub-skill]= reportsubskill &&[Sum]>=0,99))

The result is this

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

I would like how I get the total of SkillPerID column.

Can you help me please?

Thank you

[Anonymous]

Hi Jimmy,

I already finish this and now I have the following table:

I use this measure: