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.
Hi Everyone,
I am having trouble getting a calculation that works like a COUNTIF in PowerBI.
If this is my database
and I am looking for and end result like this
In Process | 1 |
Rejected | 1 |
Carrier Invoice Posted in SAP ERP | 1 |
Since Carrier Reference "ALBEMA2-2040596" has duplicates with Life Cycle Status = "Rejected" that i do not need them to be counted.
I currenlty have this DAX formula which is not helping much
Hi @Anonymous ,
Due to ALBEMA2-2040596 has Life Cycle Status:Carrier Invoice Posted in SAP ERP,and you just want to know the count for the times it was payed,so ignore ALBEMA2-2040596 forother Life Cycle Status ,right? If so ,try the following Steps:
Step1,use the following measure:
test =
VAR Life =
MAX ( 'RawData'[Life Cycle Status] )
VAR new1 =
CALCULATE (
COUNT ( RawData[Carrier Reference] ),
FILTER (
ALL ( RawData ),
RawData[Carrier Reference] = MAX ( RawData[Carrier Reference] )
&& RawData[Life Cycle Status] = "Carrier Invoice Posted in SAP ERP"
)
)
VAR NEW2 =
IF ( NEW1 = 1, "Carrier Invoice Posted in SAP ERP", Life )
RETURN
NEW2
Step 2,create new column based on test:
TESTCOLUMN =
RawData[test]
Step3, create new measure :
your_measurefinal =
CALCULATE (
DISTINCTCOUNT ( RawData[Carrier Reference] ),
FILTER ( ALL ( RawData ), RawData[TESTCOLUMN] = MAX ( RawData[TESTCOLUMN] ) )
final you will see :
Click here to download pbix if you need.
Best Regard
Lucien Wang
Hi @Anonymous ,
I may be oversimplifying your scenario, but I believe a simplification of your measure should work fine:
_yourMeasure = DISTINCTCOUNT(RawData[Carrier Reference])
When you apply this using [Life Cycle Status] as a dimension it should give you what you want.
Pete
Proud to be a Datanaut!
wow....I am not sure why was i overcomplicating things, i feel dumb!!
Not dumb - I do it ALLLL the time!
Glad it's working for you 🙂
Pete
Proud to be a Datanaut!
I know why was not working in the beginning. while testing data for example
ALBEMA2-2040596 Rejected status is getting counted and that is exactly what I DO NOT need. Does this make sense?
Not sure.
Do you mean that as soon as ANY [Carrier Reference] is rejected, you don't want any more to be counted?
...or do you mean that you want all others to be counted but not ALBEMA2-2040596 specifically?
...or do you mean it works just fine now, but you found why is wasn't working for you originally?
Pete
Proud to be a Datanaut!
I found why the simple DISTINCTCOUNT is not working for what I want to achieve.
The problem is that the invoice perhaps was sent twice, it was once rejected and once payed. for this particular analysis i just want to know the count for the times it was payed
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |