Need help in measure or column to find the comparison between the each environment

[Navaneetharaju_]

Hi ,

I have only one table with the all the data. the table contains pipelines which are available in all the environment ( Dev, Test, Prod).

I want the measure or column to find the pipeline which are not available in the environment based on few scenarios, 

I have mentioned those scenories here.

I need a Missing Column/measure -

Pipeline Name	Environment	Missing
Fb_Pipeline	Dev	Available in Dev, Test, Prod
Fb_Pipeline	Test	Available in Dev, Test, Prod
Fb_Pipeline	Prod	Available in Dev, Test, Prod
Workday_pipeline	Test	Mising in Dev
Workday_pipeline	Prod	Missing in Dev
Team_pipeliene	Prod	Missing in Dev, Test

given few more clear expected output manually written in excel.

AB, AC,AZ,AR,AE,AT,AY are pipeline

Dev	Test	Prod
AB	AB	AB	Correct
AC		AC	Test Missing
	AZ	AZ	Dev Missing
		AR	Dev/Test Missing
	AE		Dev Missing
AT			Correct
AY	AY		Correct

Thanks you.

[MFelix · Accepted solution]

HI @Navaneetharaju_ ,

You need to create a table with the ID of the enviroments something similar to this:

This table will be related with your pipelines tables:

Now add the following measure:

Pipeline Status =
VAR Enviroments =
    ALL ( Environment )
VAR PipelinesTable =
    FILTER (
        ALL ( Pipelines ),
        Pipelines[Pipeline_Name] = MAX ( Pipelines[Pipeline_Name] )
    )
VAR TotalPipelineValues =
    MAXX (
        SUMMARIZE ( PipelinesTable, Environment[EnviromentID] ),
        Environment[EnviromentID]
    )
RETURN
    IF (
        ISINSCOPE ( Environment[Enviroment] ),
        SELECTEDVALUE ( Pipelines[Pipeline_Name] ),
        SWITCH (
            TRUE (),
            COUNTROWS ( PipelinesTable ) = TotalPipelineValues, "Correct",
            COUNTROWS ( PipelinesTable ) < TotalPipelineValues,
                CONCATENATEX (
                    EXCEPT (
                        DISTINCT (
                            SELECTCOLUMNS (
                                FILTER ( Enviroments, Environment[EnviromentID] <= TotalPipelineValues ),
                                "ENV", Environment[Enviroment]
                            )
                        ),
                        DISTINCT ( SELECTCOLUMNS ( PipelinesTable, "ENV", Pipelines[Environment] ) )
                    ),
                    [ENV],
                    "/"
                ) & " Missing"
        )
    )

[Navaneetharaju_]

Hi @MFelix ,

Can i have this as a calculated column, so that i can plot this value in X axis in Bar chart.

[MFelix]

Hi @Navaneetharaju_ ,

Believe that when you refer that you want to have this on a bar chart you want to count the number of projects within each category correct?

Create a table with the status you can have:

Then add the following measure:

Status Count =
COUNTROWS (
    FILTER (
        SUMMARIZE (
            Pipelines,
            Pipelines[Pipeline_Name],
            "Pipeline status", [Pipeline Status]
        ),
        [Pipeline status] = SELECTEDVALUE ( 'Status update'[Status] )
    )
)

[MFelix]

Hi @Navaneetharaju_ ,

How does the example at the bottom connect to the data above?

Further more why do you have example that only have dev or prod and they are correct but in other examples they are missing?

Can you explain a little bit better the showe case?

[Navaneetharaju_]

Hi @MFelix , 

We have to consider only missing pipeline alone.

Scenerios for missing pipelies

* Available in Test and Prod but missing in Dev

* Available in Dev and Prod but missing in Test

* Available in prod but missing in Dev, Test

* Available in Test but missing in PROD

 we have only one table , we have column  as environment. with that column we have to achieve for the above scnarios.