March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to Solution.
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] )
)
@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 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
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] )
)
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
89 | |
85 | |
73 | |
49 |
User | Count |
---|---|
169 | |
144 | |
90 | |
70 | |
58 |