Forum Discussion

Alonso_96's avatar
Alonso_96
Frequent Visitor
6 years ago

DAX

Hello,

 

I am currently working with a base of Courses I take at the company. For this each worker has a user ID, the name of the assigned course and if he has the course Completed, Not Started or In process.

However, I need a formula that will show me the following:

If I have selected 5 courses for the filter, which of those 5 courses, if all are completed, will obtain a result of COMPLETED COURSES, if 1 or more of the 5 are not started, that will place COURSES IN PROCESS, and if the 5 they are in not initiated, that places COURSES NOT INITIATED.

But I need this formula to be adapted depending on how many courses I select, if I filter 5, 6 or more, that the formula is based on the Total of filtered courses. 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    Hi Alonso_96 ,

     

    Could you please share your sample data and the expected result here if you don't have any Confidential Information? Please upload your files to OneDrive for Business and share the link here.

     

    • Alonso_96's avatar
      Alonso_96
      Frequent Visitor
      6 years ago

       

      Identificador = CALCULATE(COUNT(Reporte[Título de la sección]),FILTER(Reporte,Reporte[ID (Usuario)]=EARLIER(Reporte[ID (Usuario)])),FILTER(Reporte,Reporte[Título de la sección]=EARLIER(Reporte[Título de la sección])))

       

      Estado del Curso = IF(AND(Reporte[Identificador]=1,Reporte[Artículo completado]="Y"),"Completado",IF(AND(Reporte[Identificador]=1,Reporte[Artículo completado]="N"),"No Iniciado",IF(Reporte[Identificador]>1,"En Proceso","")))

       

      This work to identify which course is completed, In process and no Initiate; but when I créate a Table in Inform and filter with multiple selection of courses, the data get wrong.

       

      But I need a formula that works according to the number of selected courses, for example:

If I select “Código de Conducta de Negocios” and “Curso Declaración de Conflicto de Interés”, it should be shown as if it were “Courses in Process” because of these courses one is Completed and the other is In Process.

If I select 2 courses that have a status of Completed, count them as “COMPLETED COURSES”

If I select 2 courses that have a Not Started status, count them as “COURSES NOT STARTED”

However, this should vary according to the selection, for example, if instead of selecting the “Conflict of Interest Declaration Course”, I select “Ergonomics Course”, it should also appear in process.

In other words, if I select 2 or more courses and they are all in the “Completed” state, count them as “Completed Courses”. If I select 2 or more courses and one of them says "Not Started" or "In Process" that all those selected count as "Courses in Process". If I select 2 or more courses and they all say "Not Started" it will count all those selected as "Not Started".

All this is in relation to the column ID (User)