SQL logic in DAX

[BishwaR]

I have a SQL logic count ( distinct(PurchaseOrder) | |SubOrder. How to translate it in DAX,

I tried as CALCULATE( COUNT(SubOrder),DISCTINCTCOUNT(PurchaseOrder) But I dId not get the correct resutl

Thanks

[selimovd]

Hey @BishwaR ,

what exactly do you want to do?

In a measure you can only get one result, so either the count of SubOrder or the Distinctcount of PurchaseOrder.

Help me a little to understand better your problem, then I'm confident that we can find a solution.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

[BishwaR]

Each Purchase Order has sub orders.

If PO 12 has three Suborders

   PO 22 has one Suborder

   PO 33 has four suborder

then I am trying to show in the report 

PO 12 has 3 suborders

PO 22 has 1 suborders

PO 33 has 4 suborder

TOTAL suborders 8

Thank you

[v-yingjl · Accepted solution]

Hi @BishwaR ,

Just use Count() directly to create measure:

Count Suborders = COUNT('Table'[Suborder])

Attached the sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[selimovd]

@BishwaR 

How does that look in the data model? Do you have one table with the PO and one table with the Suborders and they are connected with a relationship?

Or do you have one table for the Suborders with a column for the PO?

The reason I'm asking is that different data models need different approaches. If you could show a picture of the data model and the tables that would help a lot. Then I can tell you the measure.

Best regards

Denis

[BishwaR]

It is a single fact table that contains the both PurchaseOrders and the SubPurchaseOrders. So the PurchaseOrders repeats many times for each SubPurchaseOrder but there are a few PurchaseOrders that have only one SubPurchaseOrder, that is the reason I had to do a distinctcount on PurchaseOrders. For example:

PO 22   SubPO 1

PO22   SubPO  3

PO22   SubPO 4

PO 33  SubPO 1

While counting the SubPO we have to count based on the PO. SubPO1 repeats twice but they belong to two different POs so we have to count each one of them.

Thanks

[selimovd]

Hey @BishwaR ,

OK, so your table looks like this, right?:

PO	SubPO
22	1
22	3
22	4
33	1

And now what do you want to calculate? What do you expect as a result?

Best regards

Denis