How to used DAX to groupby multiple column and count the number of row in particular column?

1
votes

I have book table:

Country        Calender_Week       Book_name     CD_name
US             212019              Book A        CD 1
US             212019              Book B        CD 3
US             212019              Book B        CD 1
US             212019              Book C
US             212019              Book D
UK             212019              Book A
UK             212019              Book B        CD 2
UK             212019              Book B
UK             212019              Book C        CD 1

I trying to used DAX power bi query to count the row, which mean i wish to group by the Country and Calander_Week.

CountBook = CALCULATE(COUNT(Book[Book_name]),GROUPBY(Book,Book[Country],Book[Calender_Week]))
CountCD = CALCULATE(COUNT(Book[CD_name]),GROUPBY(Book,Book[Country],Book[Calender_Week]))

With above code , I get the wrong result

Below is my expected Output:

Country        Calender_Week       Book_name     CD_name   CountBook     CountCD
US             212019              Book A        CD 1      5             3
US             212019              Book B        CD 3      5             3
US             212019              Book B        CD 1      5             3
US             212019              Book C                  5             3
US             212019              Book D                  5             3
UK             212019              Book A                  4             2
UK             212019              Book B        CD 2      4             2
UK             212019              Book B                  4             2
UK             212019              Book C        CD 1      4             2

Anyone can share me ideas?

3
powerbidax

3 Answers

1
votes

enter image description here

enter image description here

CountCD = CALCULATE(COUNT('Book'[CD_name]) - COUNTBLANK('Book'[CD_name]),GROUPBY('Book','Book'[Country],'Book'[Calender_Week]),ALL('Book'))

CountBook = CALCULATE(COUNT('Book'[Book_name]),GROUPBY('Book','Book'[Country],'Book'[Calender_Week]),ALL('Book'))

User All() function to avoid the filters applied to the measure.

1
votes

I also have an idea for you, with a different approach. This can be done in the query editor (M), thus your end table will look like this: Source table:

enter image description here

Final table (the group by functionality was used here):

enter image description here

For the visuals you just need to drag and drop your values into it.

0
votes

Yet another answer which would be simpler DAX measure:

CountBook = CALCULATE(
    COUNTA( Book[Book_name] ),
    ALLEXCEPT(
        Book,
        Book[Country],
        Book[Calendar_Week]
    )
)

CountCD = CALCULATE(
    COUNTA( Book[CD_name] ),
    ALLEXCEPT(
        Book,
        Book[Country],
        Book[Calendar_Week]
    )
)

These measures counts non-BLANK values of Book_name or CD_name, ignoring all filters other than those on Country and Calendar_Week.