Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
tyan
Helper II
Helper II

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

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. 

 

tyan_0-1634052990174.png

 

1 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] )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

9 REPLIES 9
smpa01
Super User
Super User

@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] )
)

 

smpa01_0-1634058242119.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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

@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] )
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
goncalogeraldes
Super User
Super User

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

@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.

 

 

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_0-1634056697444.png

Hi, Thank you again! @goncalogeraldes 

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

@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

it does not work. still show no value.

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

tyan_0-1634092178204.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors