Forum Discussion

eomedes's avatar
eomedes
Icon for Advocate I rankAdvocate I
4 years ago
Solved

Counting grouped occurrences

Hello, I make a summary of the situation.
I have a dimension table of calendar and another one of hours, and on the other hand, two tables of facts both related to those dimension tables.

 

 

 


Then, I make a measurement, where if the value A minus B is positive, 1, otherwise 0.

 

Difference A-B = IF(SUM('TABLE A'[Value Table A])-SUM('TABLE B'[Value Table B])>0,1,0)

 

Then, in a matrix, as rows I put the days of the week, as columns, the hours, and as value, the measurement made.

 

 

So far, so good.

The problem is that I want to have in a card, a counter of occurrences in which the value of the measure is 1. I want the sum of all the yellow cells.

 


I have tried to use COUNTAX, but I cannot group by time and date because they come from two independent tables (it has to be).

Thanks everyone!!

  • SpartaBI's avatar
    SpartaBI
    4 years ago

    eomedes try this:

     

     

    Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES('Days'[Date]),
                VALUES('Hour'[Hour])
            ),
            "@Test", [Difference A-B]
        ),
        [@Test] = 1
    )
)

     

     

     Or this (depandant on your business case):

     

    Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES('Days'[Day Of Week]),
                VALUES('Hour'[Hour])
            ),
            "@Test", [Difference A-B]
        ),
        [@Test] = 1
    )
)

     

     

    In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
    Showcase Report – Contoso By SpartaBI 
    Website Linkedin Facebook 
    This is SpartaBI!

1 Reply

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    eomedes try this:

     

     

    Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES('Days'[Date]),
                VALUES('Hour'[Hour])
            ),
            "@Test", [Difference A-B]
        ),
        [@Test] = 1
    )
)

     

     

     Or this (depandant on your business case):

     

    Measure = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            CROSSJOIN(
                VALUES('Days'[Day Of Week]),
                VALUES('Hour'[Hour])
            ),
            "@Test", [Difference A-B]
        ),
        [@Test] = 1
    )
)

     

     

    In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪
    Showcase Report – Contoso By SpartaBI 
    Website Linkedin Facebook 
    This is SpartaBI!