Ways to reduce the numbers of measures and conditions in calculate

[Anonymous]

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

[tamerj1]

Hi @Anonymous 

can you please provide more details about you data structure and expected results and report shape?

[Anonymous]

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.

[tamerj1]

HI @Anonymous 
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 )

[Anonymous]

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 😄

[VahidDM · Accepted solution]

HI @Anonymous 

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/

[Anonymous]

Thank you so much for the help Vahid, worked very well.