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 t...
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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Jimmy,

     

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

     

    Can you help me please?

     

    Thank you