Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

create new measure based on multiple criteria and uniqueID

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,

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    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

9 Replies

  • Jimmy801's avatar
    Jimmy801
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Jimmy,

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

      I use this measure:

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

       

      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's avatar
        Jimmy801
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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