Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Syndicate_Admin
Administrator
Administrator

Count on a measure

Good afternoon.

I have created a measure (Historical Q V/H), whose results are Q1,Q2,Q3 and Q4.

I need to know how many values of each Q there are in the measure.

Q4 Count = COUNTROWS(FILTER(VALUES('Times Integrated Marker Advisor'[Agent]),'Times Integrated Scoreboard Advisor'[Historical Q V/H]="Q4"))
I have created the above measure but the results are not consistent.

How could I do it?

Thanks a lot.

1 ACCEPTED SOLUTION

Hi @Syndicate_Admin,

 

I suggest you to add [Historical Q V/H] measure as a column in a virtule table. And then count the Agent by filter [Historical Q V/H] in that table.

Q4 Count =
VAR _Historical_Q_VH =
    ADDCOLUMNS (
        ALL ( 'Times Integrated Marker Advisor' ),
        "Historical Q V/H",
            IF (
                AND (
                    SELECTEDVALUE (
                        'Integrated Scoreboard Times Advisor'[Level 1],
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                    ) = "Historical",
                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.07
                ),
                "Q4",
                IF (
                    AND (
                        OR (
                            SELECTEDVALUE (
                                'Integrated Scoreboard Times Advisor'[Level 1],
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                            ) = "Historical",
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.07
                        ),
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.1
                    ),
                    "Q3",
                    IF (
                        AND (
                            OR (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.1
                            ),
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.15
                        ),
                        "Q2",
                        IF (
                            AND (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.15
                            ),
                            "Q1",
                            ""
                        )
                    )
                )
            )
    )
RETURN
    COUNTX ( FILTER ( _Historical_Q_VH, [Historical Q V/H] = "Q4" ), [Agent] )

 

Best Regards,
Rico Zhou

 

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

4 REPLIES 4
Syndicate_Admin
Administrator
Administrator

Good afternoon.

The result he shows me is the same, but it is not reality.

The table is as follows:

JoseMJim_0-1647522456517.png

And I create 7 measures for each value in the Level 1 field:

An example:

Historical Q V/H = IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.07),"Q4",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.07),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.1),"Q3",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.1),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.15),"Q2",IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.15),"Q1",""))))
Now I want to make a count of each of the 7 measurements, whose values are always Q1,Q2,Q3 and Q4.
I will have to do a total of 28 counting measures (7x4) to be able to know how many advisors there are with the Q1 of Level 1 = Historica for example.
Thanks a lot.
amitchandak
Super User
Super User

@Syndicate_Admin , Try like

 

Q4 Count = Countx(FILTER(VALUES('Times Integrated Marker Advisor'[Agent]),'Times Integrated Scoreboard Advisor'[Historical Q V/H]="Q4"),[Agent])

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Good afternoon.

The result he shows me is the same, but it is not reality.

The table is as follows:

JoseMJim_0-1647523575603.png

And I create 7 measures for each value in the Level 1 field:

An example:

Historical Q V/H = IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.07),"Q4",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.07),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.1),"Q3",IF(and(or(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.1),'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]<0.15),"Q2",IF(and(SELECTEDVALUE('Integrated Scoreboard Times Advisor'[Level 1],'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor])="Historical",'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]>=0.15),"Q1",""))))
Now I want to make a count of each of the 7 measurements, whose values are always Q1,Q2,Q3 and Q4.
I will have to do a total of 28 counting measures (7x4) to be able to know how many advisors there are with the Q1 of Level 1 = Historica for example.
Thanks a lot.

Hi @Syndicate_Admin,

 

I suggest you to add [Historical Q V/H] measure as a column in a virtule table. And then count the Agent by filter [Historical Q V/H] in that table.

Q4 Count =
VAR _Historical_Q_VH =
    ADDCOLUMNS (
        ALL ( 'Times Integrated Marker Advisor' ),
        "Historical Q V/H",
            IF (
                AND (
                    SELECTEDVALUE (
                        'Integrated Scoreboard Times Advisor'[Level 1],
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                    ) = "Historical",
                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.07
                ),
                "Q4",
                IF (
                    AND (
                        OR (
                            SELECTEDVALUE (
                                'Integrated Scoreboard Times Advisor'[Level 1],
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                            ) = "Historical",
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.07
                        ),
                        'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.1
                    ),
                    "Q3",
                    IF (
                        AND (
                            OR (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.1
                            ),
                            'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] < 0.15
                        ),
                        "Q2",
                        IF (
                            AND (
                                SELECTEDVALUE (
                                    'Integrated Scoreboard Times Advisor'[Level 1],
                                    'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor]
                                ) = "Historical",
                                'Times Integrated Scoreboard Advisor'[SALE/HOUR ENERGY Advisor] >= 0.15
                            ),
                            "Q1",
                            ""
                        )
                    )
                )
            )
    )
RETURN
    COUNTX ( FILTER ( _Historical_Q_VH, [Historical Q V/H] = "Q4" ), [Agent] )

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.