Which Dax function can calculate the amount of unique condition based on certain criteria

[tyan]

Someone please help, Im new to powerbi. Not sure how to set a dax function to calcuate and filter out certain condition.   I used distinctcount dax function but it counts the total amount of cancel; however if any of these : ok/supplier suppower to do/ add quanity with cancel  etc..   then shouldn't consider as cancel.

I woud like to see how many Model Number is Cancel. The Cancel criteria is only have "Cancel". Those with OK/Cancel/Supplier support to do/add quantity etc isnt considered Cancellation.  

The Final Analysis is the result that im looking for. For example: Nike model# 101266 is none cancel since it has ok and supplier support to do. PUMA #117464 and #171917 is cancelled since they only got Cancel, so the total amount of cancellation for Puma is 2. (2 model)  

which dax function should I use. I really apprecaite your help. Im so struggling with it. 

[smpa01]

@tyan  try this out

Measure2 =
CALCULATE (
    COUNTROWS (
        CALCULATETABLE (
            'Fact',
            EXCEPT (
                SUMMARIZE ( FILTER ( 'Fact', 'Fact'[Analysis] = "Cancel" ), 'Fact'[Model] ),
                SUMMARIZE ( FILTER ( 'Fact', 'Fact'[Analysis] <> "Cancel" ), 'Fact'[Model] )
            )
        )
    ),
    REMOVEFILTERS ( 'Fact'[Analysis] )
)

[tyan]

@smpa01 Thank you for helping me  ! this one works for me. Can I also do distinct count? for example, this same  model number have 4 times cancellation but i only count it once.   

Model #123 Cancel

Model #123 Cancel

Model #123 Cancel

Total Model #123 Cancellation : 1

[smpa01 · Accepted solution]

@tyan 

Measure2 =
CALCULATE (
    CALCULATE (
        DISTINCTCOUNT ( 'Fact'[Model] ),
        CALCULATETABLE (
            'Fact',
            EXCEPT (
                SUMMARIZE ( FILTER ( 'Fact', 'Fact'[Analysis] = "Cancel" ), 'Fact'[Model] ),
                SUMMARIZE ( FILTER ( 'Fact', 'Fact'[Analysis] <> "Cancel" ), 'Fact'[Model] )
            )
        )
    ),
    REMOVEFILTERS ( 'Fact'[Analysis] )
)

[goncalogeraldes]

Hello there @tyan ! Try this:

Cancelled =
CALCULATE(
DISTINCTCOUNT(Table[Model Number]),
FILTER( Table, [Final Analysis] = "Cancel")
)

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[tyan]

@goncalogeraldes  Hi thank you for helping me. Sorry for misleading.. the actual data doesnt have [final analysis] this column. I just showed it here in order to further explain my question. 

Is there way to calcuate the amount of model number that is completely cancelled  in powerbi ? 

I apologize for my explaination.. it is hard to understand.

[goncalogeraldes]

Hello there @tyan ! I am not sure if this fills your needs but try this: 

Cancelled =
var selected_model = 
SELECTEDVALUE(Table[Model Number])

var _count =
CALCULATE(
COUNT(Table[Model Number]),
FILTER( Table, [Model Number] = selected_model)
)

return 
IF(
_count > 1,
DISTINCTCOUNT(Table[Model Number]))

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[tyan]

Hi, Thank you again! @goncalogeraldes 

I did exactly what you showed me, but it didnt show any value. I maybe wrong

[goncalogeraldes]

@tyan I think its my formula that is wrong, try this:

Cancelled =
var selected_model = 
SELECTEDVALUE(Table[Model Number])

var _count =
CALCULATE(
COUNT(Table[Model Number]),
FILTER( Table, [Model Number] = selected_model)
)

return 
IF(
_count = 1,
CALCULATE( COUNT( Table[Model Number] ) )
)

If this does not work, could you please provide some sample data after removing any sensible data?

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[tyan]

it does not work. still show no value.

not sure how to upload file. but this is the screeshot.