CODEWITHSUNDEEP
powerbidax
Sam.H
Sam.H

Reputation: 327

Count Occurances of value in 2 columns

I have a simple job to do in PowerBi but for some reason I can not get my head around it.

I have projects table - in this table we have "date of start" and "date of end". What I am after is a smaller table with "Year & Quarter" first column and a count of projects started and count of project ended in that "Year & Quarter".

Project Number Started Ended
xxxx23 2019-01 2019-03
xxxx24 2019-03 2020-01
xxxx25 2019-03 2020-02

what I am after is something like below:

Year & Quarter Project Started Project Ended
2019-01 1 0
2019-03 2 1
2019-04 0 0

When doing the calculations, I am getting wrong counts. Any help is really appreciated!

Upvotes: 0

Views: 79

Answers (2)

smpa01
smpa01

Reputation: 4346

If you have a Calendar Table, you can achieve the ask by doing following two ways

#1

newTable = 
VAR _1 =
    SUMMARIZE ( 'Calendar', 'Calendar'[Yr & Qt] )
VAR _2 =
    ADDCOLUMNS (
        ADDCOLUMNS (
            _1,
            "Project Started",
                COUNTX (
                    FILTER ( Projects, Projects[started] = EARLIER ( [Yr & Qt] ) ),
                    [started]
                )
        ),
        "Project Ended", COUNTX ( FILTER ( Projects, Projects[ended] = EARLIER ( [Yr & Qt] ) ), [ended] )
    )
RETURN
    _2

#2

Table 2 = 
ADDCOLUMNS (
    ADDCOLUMNS (
        SUMMARIZE ( 'Calendar', 'Calendar'[Yr & Qt] ),
        "Project Start Count",
            CALCULATE (
                CALCULATE (
                    COUNTX ( Projects, Projects[started] ),
                    TREATAS ( SUMMARIZE ( 'Calendar', 'Calendar'[Yr & Qt] ), Projects[started] )
                )
            )
    ),
    "Project End Count",
        CALCULATE (
            CALCULATE (
                COUNTX ( Projects, Projects[ended] ),
                TREATAS ( SUMMARIZE ( 'Calendar', 'Calendar'[Yr & Qt] ), Projects[ended] )
            )
        )
)

results in following

SOlution

The solution has minimum dependency to a Calendar tbl with a column like Yr & Qt as following

Solution

Upvotes: 0

Wouter
Wouter

Reputation: 2976

You can use UNION to create a new table. You can use the original table twice but with different columns.

NewTable = 
    UNION(
        SELECTCOLUMNS( Projects,
                        "date",Projects[started],
                        "project",Projects[project],
                        "Started",1,
                        "Ended",0
                    )
        ,
        SELECTCOLUMNS( Projects,
                        "date",Projects[ended],
                        "project",Projects[project],
                        "Started",0,
                        "Ended",1
                    )
        )

Upvotes: 1

Related Questions