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, I need help to calculate some sales but cannot work it out.
Sample table as below:
When "ranking by bill month" has value 1, all "invoice_amt" of "customer_id" is a "keep". The rest is not.
I cannot figure out an IF function for this. Appreciate any help or advice!
Solved! Go to Solution.
Hi @Anonymous ,
Based on my test, you could refer to below steps:
Sample data:
Create below measure:
Measure = var a = CALCULATE(MAX('Table1'[id]),FILTER(ALL('Table1'),'Table1'[ranking by bill month]=1)) return IF(MAX('Table1'[id])=a,"Keep",BLANK())
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Anonymous ,
Based on my test, you could refer to below steps:
Sample data:
Create below measure:
Measure = var a = CALCULATE(MAX('Table1'[id]),FILTER(ALL('Table1'),'Table1'[ranking by bill month]=1)) return IF(MAX('Table1'[id])=a,"Keep",BLANK())
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
hi @v-danhe-msft , thank you so much for your help. However, when I tried it with a larger data set, it does not work correctly the way I need it. The field "ranking by bill month" actually resets per "id"; so different "id" will have the same ranking structure.
I tried your measure but it only works with id:2041. Basically, I just want to keep the "invoice_amt" of all "id" whenever the "id" has a 1 in the ranking by bill month".
I hope I explain well enough. I am still learning Power BI as I go.
I tried this measure below and it works now, credit to @v-jiascu-msft. I will test it for the whole data set.
Measure =
IF (
"1"
IN CALCULATETABLE (
VALUES ( Sheet1[ranking by bill month] ),
ALLEXCEPT ( Sheet1, Sheet1[id])
),
"Keep",
"Not"
)
Thank you all,
Hi @Anonymous ,
It's pleasant that your problem has been solved, could you please mark my reply as Answered to close this topic?
Regards,
Daniel He
Perhaps:
IF([ranking by bill month] = 1,[ranking by bill month],BLANK())
Hi @Greg_Deckler , thank you for your suggestion but this DAX only let me keep the "invoice_amt" of "customer_id" that has "ranking by bill month" =1; other "invoice_amt" of the same "customer_id" in this case will be blank. I need to include them all as long as a customer_id has a ranking 1 in their record.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |