count distinct with criteria in measure

[rezaaditia]

Hi All -

please help, i still don't understand using measure to calculate count with multiple criteria. below is my sample query

COUNTD(
IF DATE([po_release_supplier2]) = DATE([Validate Date])
    AND DATE([sap_insert_date]) = DATE([Validate Date]) 
    AND [Validate Source] = "outflow"  THEN
 [Purchasing Document]
END
)

thanks in advance

[Mariusz]

Hi @rezaaditia 

Can you provide a sample of data you working with and the end goal of what you wont to achive.

Best Regards,
Mariusz

Please feel free to connect with me.

[rezaaditia]

Hi @Mariusz 

I am really sorry, i can't share my data, and i don't know also how to replicate the data. Basically i have 1 reports with multiple colums with table name : po report

i want to count (distinct) number of backlog based on filter like below

[po_release_supplier2]=[validate_date] and 

[sap_insert_date] = [validate_date] and

[validate_source] ="backlog"

in excel, it will be like

iF(And([po_release_supplier2]=[validate_date],
[sap_insert_date] = [validate_date],
[validate_source] ="backlog"),
count(PO),
"")

i want to use the measure, once the formula work, so i just use card visualization to shows the number

thanks

[v-piga-msft · Accepted solution]

Hi @rezaaditia ,

Based on your logic, you could try the measure below.

Measure =
IF (
    MAX ( [po_release_supplier2] ) = MAX ( [validate_date] )
        && MAX ( [sap_insert_date] ) = MAX ( [validate_date] )
        && MAX ( [validate_source] ) = "backlog",
    COUNT ( [PO] ),
    ""
)

If you still need help, you'd better create the sample data which could reproduce your scenario and paste here as table format so that we could have a test on it.

Best Regards,

Cherry

[Mariusz]

Hi @rezaaditia 

Sorry but its very difficult to imagine what you whant to achive by looking only at excel formula.

Can you not create a sample with the same structure as the data you are working on?

All I need is few lines so I can see the logic.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.