topic Re: dax query to count the number of quarters for different years in DAX Commands and Tips

dax query to count the number of quarters for different years

Haripinnoju — Fri, 23 Jun 2023 08:22:27 GMT

Hi,
I need a dax query that count the number of quarters based on the quarters and years data, it must not be distinct.
eg. year 2020 quarters Q1, Q2, Q3, Q4 No.of quarters: 4

if selected 2years in the slicer then:

2020 has four quarters data called Q1, Q2, Q3, Q4

2021 has three quarters data called Q1, Q2, Q3

so, the overall count of quarters must be 7.

Similarly, for all the selected years. - count of years and each year how many quarters it is has.

It needs to show the count. It should not count the blanks.

here- when taken distinct count of quarter it shows 5 which is not expected, when taken count of quarter it shows the count of quarter records and led to 99 count. I expect the count to be 8- 2years and they have 4quarters each so the count should be 2*4=8.

Thank you for your help in advance!

Re: dax query to count the number of quarters for different years

tamerj1 — Fri, 23 Jun 2023 08:37:50 GMT

Hi Haripinnoju

please try

Measure8 =
COUNTROWS (
SUMMARIZE (
'vcontacts and TO SUPPLIER DOC',
'vcontacts and TO SUPPLIER DOC'[QUARTER],
'vcontacts and TO SUPPLIER DOC'[YEAR]
)
)

Re: dax query to count the number of quarters for different years

devanshi — Fri, 23 Jun 2023 08:39:21 GMT

Your query is right only write COUNTROWS instead of DISTINCTCOUNT

Re: dax query to count the number of quarters for different years

Haripinnoju — Fri, 23 Jun 2023 08:48:24 GMT

thank you,
how to remove the blank quarter count from it.
It must show only 8. It should count only the quarter which has data in it.

Re: dax query to count the number of quarters for different years

tamerj1 — Fri, 23 Jun 2023 09:50:27 GMT

Haripinnoju

Measure19 =
COUNTROWS (
FILTER (
SUMMARIZE (
'vcontacts and TO SUPPLIER DOC',
'vcontacts and TO SUPPLIER DOC'[QUARTER],
'vcontacts and TO SUPPLIER DOC'[YEAR]
),
'vcontacts and TO SUPPLIER DOC'[QUARTER] <> BLANK ()
)
)