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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.