Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello guys, i'm working on a dashboard for my work to control some indicators about project evolution.
We already have some spreadsheets where we put the data in, and i'm working with power query + power bi to extract the data from the spreadsheet and display in a more pleasant way in power bi. Happens that we have a lot of stages in our projects and status for each stage in each project, and the only way i could thought about is to make measures for each status in each stage, so i can gather the numbers of stages completeds and missing in schedule, but i would end up with like 60 measures. You could help me with a workaround or some tip to improve my model and not have to make this 60 measures? I'll appreciate all the help i can get. Down belown i put the standard measure i made to get the numbers of project in each status.
The only thing i change between measures is the number (in this case 5) and Conjunto de etapas - Geral'[Etapa] (in this case "Materiais").
2. Cont Materiais 5 = IF(
SELECTEDVALUE(
'Conjunto de etapas - Geral'[Company])=BLANK(),
CALCULATE(
COUNTROWS('Conjunto de etapas - Geral'),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Status Conc] = 5),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Stage]="Materiais")),
IF(
CALCULATE(
COUNTROWS('Conjunto de etapas - Geral'),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Status Conc] = 5),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Company] = SELECTEDVALUE('Conjunto de etapas - Geral'[Company])),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Stage]="Materiais"))=BLANK(),
0,
CALCULATE(
COUNTROWS('Conjunto de etapas - Geral'),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Status Conc] = 5),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Company] = SELECTEDVALUE('Conjunto de etapas - Geral'[Company])),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Stage]="Materiais"))
))
Solved! Go to Solution.
HI @glawberc
Try this:
Cont Materiais 5 =
VAR _A =
CALCULATE(
COUNTROWS( 'Conjunto de etapas - Geral' ),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Status Conc] = 5
&& 'Conjunto de etapas - Geral'[Stage] = "Materiais"
)
)
RETURN
IF( ISBLANK( _A ), 0, _A )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @glawberc
can you please provide more details about you data structure and expected results and report shape?
My data is composed by columns with Project ID, Stage, Status of Beginning, Expected Beginning Date, Real Date of Beginning and the same columns for conclusion dates. I have to dispose this data in a dashboard to evidence the progression of those projects based on some premisses that we have here. So in resume i need to count those current status of conclusion and dispose them as integer numbers and a percentage of total projects within this year.
The major problem is that i have 5 status (1 to 5 numbers), and 12 stages of project if i do a measure to count all of this status in each 12 stages i would end up with 60 measures, what is a huge number to control. In this way i'm looking for a solution that i don't have to end up with a table with 60 measures inside.
HI @glawberc
Why don't you just use slicers to select the required output. Then your measure would simply be
Cont Materiais =
VAR _Count =
COUNTROWS ( 'Conjunto de etapas - Geral' )
RETURN
IF ( ISBLANK ( _Count ), 0, _Count )
I got a different way but i could resolve that. I've made a matrix with status numbers in a column and the measure made by Vahid on the other, worked very fine. Thanks for the help 😄
HI @glawberc
Try this:
Cont Materiais 5 =
VAR _A =
CALCULATE(
COUNTROWS( 'Conjunto de etapas - Geral' ),
FILTER(
'Conjunto de etapas - Geral',
'Conjunto de etapas - Geral'[Status Conc] = 5
&& 'Conjunto de etapas - Geral'[Stage] = "Materiais"
)
)
RETURN
IF( ISBLANK( _A ), 0, _A )
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thank you so much for the help Vahid, worked very well.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |