Forum Discussion

MPG1907's avatar
MPG1907
New Member
2 years ago
Solved

undefined

Hi I am new to Power BI. I need to analyse data about language classes attendance. I need to analyse how many people attended each class each month, their nationality and gender for last 5 years. Do I put the table with different languages in fact table and in dimension tables I put data per year or per subject? In star schema, can I have only two tables?

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hola MPG1907. Recomiendo que crees dos consultas adicionales.

    La primera, una consulta que sea Class code, para poder relacionar con Class Sub-Code, debes quitarle los numeros a partir del guion medio.

     

    Y la segunda tabla, es la tabla calendario. Con este formula DAX la puedes crear:

     

    TablaCalendario =
    VAR T_Calendario =
        ADDCOLUMNS(
            CALENDARAUTO(),
            "Año", YEAR([Date]),
            "Semestre", "Semes. " & CEILING(MONTH([Date])/6, 1),
            "Cuatrimestre", "Cuatr. " & CEILING(MONTH([Date])/4, 1),
            "Trimestre", "Trim. " & CEILING(MONTH([Date])/3, 1),
            "Bimestre", "Bim. " & CEILING(MONTH([Date])/2, 1),
            "Mes Número", MONTH([Date]),
            "Mes Nombre", FORMAT([Date], "MMMM"),
            "Semana", "Sem. " & WEEKNUM([Date], 2),  -- El número 2 indica que la semana comienza en lunes
            "Día Número", WEEKDAY([Date],2),
            "Día Nombre", FORMAT([Date], "dddd")
        )
    RETURN
        T_Calendario
     
    Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi MPG1907 ,

     

    First of all, thank you andrezmar and qqqqqwwwweeerrr for the quick reply. 

    I have some other ideas for you to consider. Based on your test data, I think a pie chart would be a good option for you. You can visualize the various percentages.

    If you want to display the most popular Activity name as a visual object of the card then you can create measures.

     

    Measure = COUNT('classes delivered in 2023'[Activity name])
    Measure 2 = 
var _table=SUMMARIZE(ALL('classes delivered in 2023'),[Activity name],"count",[Measure])
var _max= MAXX(_table,[count])
RETURN MAXX(FILTER(_table,[count]=_max),[Activity name])

     

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

5 Replies

    • MPG1907's avatar
      MPG1907
      New Member
      2 years ago

      Hi,

      Thank you for your reply.

      Here is sample data: 

      1. classes with codes

      Class codeActivity name Course fee in $ ConsultantsLive/virtual
      LC-2438English $                     50,00XYLive Classroom Instruction
      LC-6323French $                     50,00XY1Live Classroom Instruction
      LC-7218German $                     50,00XY3Live Classroom Instruction
      LC-7266Spanish $                     50,00XY4Live Classroom Instruction
      LC-7058Italian $                     50,00XY4Live Classroom Instruction
      LC-6261Chinese $                     50,00XY6Virtual Classroom Instruction
      LC-6788Arabic $                     50,00XY7Virtual Classroom Instruction
      LC-7267Russian $                     50,00XY8Virtual Classroom Instruction
      LC-6330Portugese $                     50,00XY9Virtual Classroom Instruction
      LC-6677Dutch $                     50,00XY3Virtual Classroom Instruction

      2. classes delivered in 2023

      Activity nameClass  Sub-CodeTypeStart DateEnrolledMaleFemale
      EnglishLC-2438-19Live Classroom Instruction19.09.202315105
      EnglishLC-2438-13Live Classroom Instruction25.01.202320812
      EnglishLC-2438-14Live Classroom Instruction1.10.202319910
      EnglishLC-2438-15Live Classroom Instruction1.11.2023624
      EnglishLC-2438-16Live Classroom Instruction13.02.2023734
      EnglishLC-2438-17Live Classroom Instruction27.06.20231688
      EnglishLC-2438-18Live Classroom Instruction9.12.202319910
      EnglishLC-6966-20Live Classroom Instruction30.10.2023322012
      FrenchLC-6323-12Live Classroom Instruction24.03.202315510
      FrenchLC-6323-13Live Classroom Instruction22.06.20231899
      FrenchLC-6323-14Live Classroom Instruction11.08.2023231112
      GermanLC-7218-8Live Classroom Instruction3.02.2023361620
      GermanLC-7218-9Live Classroom Instruction21.09.202319811
      SpanishL-7266-10Live Classroom Instruction4.06.20231266
      SpanishLC-7266-11Live Classroom Instruction9.09.20231468
      SpanishLC-7266-12Live Classroom Instruction3.11.2023231112
      ItalianLC-7058-1Live Classroom Instruction28.04.202320911
      ItalianLC-7058-2Live Classroom Instruction10.06.20231596
      ItalianLC-7058-3Live Classroom Instruction28.10.20231367
      ChineseLC-6261-22Virtual Classroom Instruction14.06.20231376
      ChineseLC-6261-23Virtual Classroom Instruction19.09.2023201010
      ChineseLC-6261-24Virtual Classroom Instruction13.11.20231275
      ArabicLC-6788-7Virtual Classroom Instruction4.06.2023945
      ArabicLC-6788-8Virtual Classroom Instruction19.09.20231055
      ArabicLC-6788-9Virtual Classroom Instruction23.10.20231275
      ArabicLC-6788-10Virtual Classroom Instruction24.11.20231257
      RussianLC-7058-11Virtual Classroom Instruction24.03.2023734
      RussianLC-7058-12Virtual Classroom Instruction22.06.2023835
      PortugeseLC-6330-4Virtual Classroom Instruction9.09.2023514
      DutchLC-6677-2Virtual Classroom Instruction3.02.2023633

      I have same date for other years. 

      I am interested to see what course were most popular, gender distribution, which format was most popular, which consultant was engaged the most, etc.. 

      Thanks in advance. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hola MPG1907. Recomiendo que crees dos consultas adicionales.

        La primera, una consulta que sea Class code, para poder relacionar con Class Sub-Code, debes quitarle los numeros a partir del guion medio.

         

        Y la segunda tabla, es la tabla calendario. Con este formula DAX la puedes crear:

         

        TablaCalendario =
        VAR T_Calendario =
            ADDCOLUMNS(
                CALENDARAUTO(),
                "Año", YEAR([Date]),
                "Semestre", "Semes. " & CEILING(MONTH([Date])/6, 1),
                "Cuatrimestre", "Cuatr. " & CEILING(MONTH([Date])/4, 1),
                "Trimestre", "Trim. " & CEILING(MONTH([Date])/3, 1),
                "Bimestre", "Bim. " & CEILING(MONTH([Date])/2, 1),
                "Mes Número", MONTH([Date]),
                "Mes Nombre", FORMAT([Date], "MMMM"),
                "Semana", "Sem. " & WEEKNUM([Date], 2),  -- El número 2 indica que la semana comienza en lunes
                "Día Número", WEEKDAY([Date],2),
                "Día Nombre", FORMAT([Date], "dddd")
            )
        RETURN
            T_Calendario
         
        Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hola MPG1907. Para analisis donde solo se tiene una fuente de datos y las columnas de genero, nacionalidad y clases, es necesario tener una columna en formatos de fechas. Esta columna de fechas, el power bi desktop le genera una jerarquia de fechas que permite analizar por tus datos por año, trimestre y mes.

     

    Si quieres hacer el analisis por otra temporalidad como nombre del dia, semana o semestre. Se debe crear una tabla calendario que vaya relacionada con tus datos. La tabla calendario tambien es necesaria cuando se tiene diferentes fuentes de datos.

     

    Si está satisfecho con esta respuesta, márquela como solución para que otros la encuentren.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi MPG1907 ,

     

    First of all, thank you andrezmar and qqqqqwwwweeerrr for the quick reply. 

    I have some other ideas for you to consider. Based on your test data, I think a pie chart would be a good option for you. You can visualize the various percentages.

    If you want to display the most popular Activity name as a visual object of the card then you can create measures.

     

    Measure = COUNT('classes delivered in 2023'[Activity name])
    Measure 2 = 
var _table=SUMMARIZE(ALL('classes delivered in 2023'),[Activity name],"count",[Measure])
var _max= MAXX(_table,[count])
RETURN MAXX(FILTER(_table,[count]=_max),[Activity name])

     

     

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.