Distinct Count with grouping on product and date

[gshaikh]

Hello,

Please help me on creating a measure for distinct count but it shoud be group with product and shippeddate 

here is my sql code for that 

SELECT COUNT(ORDERNO),ShippedDate
,KSCH
FROM
(SELECT SBUCode,
COUNT(ORDERNO) CNT,
OrderNo,
KSCH,
ShippedDate,
countrycode
FROM ADMIN.BPCS_OTS
WHERE countrycode='US'
and extract(month from shippeddate)=9
and extract(year from shippeddate)=2022
and sbucode='ADS'
GROUP BY SBUCode,
OrderNo,
KSCH,
ShippedDate,
countrycode)
GROUP BY ShippedDate
,KSCH

Yous suggestions would be a great help!!

Thanks 

Irshad Shaikh

[v-shex-msft]

Hi @gshaikh,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[gshaikh]

Hello,

Please see below We need 4 as an output as oracle output has 4 rows of output Request you to please find below dummy data 

Thank you so much for help 

Data
Orderno	OrderLineNumber	Shippeddate	ScheduleDate
1232	4	14-11-2022	20211113
2323	6	16-11-2022	20221115
2323	5	16-11-2022	20221115
2323	7	16-11-2022	20221115
2323	8	16-11-2022	20221115
6556	6	24-11-2022	20211113
4543	7	23-11-2022	20221115
4543	8	23-11-2022	20221115

Query in oracle
SELECT COUNT(ORDERNO) CNT, SCHEDULEDATE, ShippedDate FROM TABLE1 GROUP BY ORDERNO, SCHEDULEDATE, ShippedDate

Output In Oracle
Cnt	ScheduleDate	Shippeddate
1	14-11-2022	20211113
4	16-11-2022	20221115
1	24-11-2022	20211113
2	23-11-2022	20221115

Power Bi Output
4	4 Should be output as above Oracle output has 4 rows

[v-shex-msft]

HI @gshaikh,

You can try to use the following measure formal if it helps:

formula =
CALCULATE (
    COUNT ( 'Table'[ORDERNO] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        [countrycode] = "US"
            && MONTH ( [shippeddate] ) = 9
            && YEAR ( [shippeddate] ) = 2022
            && [sbucode] = "ADS"
    ),
    VALUES ( 'Table'[KSCH] ),
    VALUES ( 'Table'[shippeddate] )
)

If the above not help, can you please share some dummy data with raw table structure and expected results? They will help us clarify your data structure and scenario and test coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[gshaikh]

Hello,

Unfortunatly No I AM not getting expected output above dax was clubing it and showing total value but We need count rows not total Can you please help me Please 

here is the query in sql if I add extra column then my rows count will get incresed so we are not adding extra columns just few which mentioned in below query

SELECT COUNT(ORDERNO) CNT,
KSCH,
ShippedDate
FROM ADMIN.BPCS_OTS
GROUP BY ORDERNO,
KSCH,
ShippedDate

Thanks & Regards,

Irshad Shaikh