Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
MPG1907
New Member

undefined

‎08-09-2024 07:55 AM

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?

Labels:
Message 1 of 6
490 Views
0
2 ACCEPTED SOLUTIONS
andrezmar
Resolver I
Resolver I
In response to MPG1907

‎08-12-2024 09:46 AM

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.

View solution in original post

Message 4 of 6
387 Views
0
Anonymous
Not applicable

‎08-14-2024 12:37 AM

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.

vtangjiemsft_0-1723620099637.png

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])

 

vtangjiemsft_1-1723621024928.png

 

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. 

View solution in original post

undefined.pbix
Message 6 of 6
361 Views
0
5 REPLIES 5
Anonymous
Not applicable

‎08-14-2024 12:37 AM

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.

vtangjiemsft_0-1723620099637.png

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])

 

vtangjiemsft_1-1723621024928.png

 

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. 

undefined.pbix
Message 6 of 6
362 Views
0
andrezmar
Resolver I
Resolver I

‎08-10-2024 11:02 AM

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.

Message 5 of 6
430 Views
0
qqqqqwwwweeerrr
Super User
Super User

‎08-09-2024 09:21 AM

Hi @MPG1907 

 

Can you share some sample data along with expected output

 

Regards

 

Message 2 of 6
455 Views
0
MPG1907
New Member
In response to qqqqqwwwweeerrr

‎08-12-2024 08:17 AM

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. 

Message 3 of 6
392 Views
0
andrezmar
Resolver I
Resolver I
In response to MPG1907

‎08-12-2024 09:46 AM

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.
Message 4 of 6
388 Views
0

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

September Power BI Update Carousel

Power BI Monthly Update - September 2025

Check out the September 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All