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 PBI Guru's
Need help please, I know what my logic should be, I know what my result should be, but I am unable to build any formula to show the result I need.
Below is the data set up and three different scenarios. The column named [Primary File Number line TAG] is where I would like to add the formula.
Hope I can get someone to figure this out.
Solved! Go to Solution.
hi, @Anno2019
You could use this formula to create a new column as below:
Primary File Number line TAG = var countsales=CALCULATE(COUNTA('Table'[Account Owner Role]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return var highestinvoice=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return IF(countsales<>BLANK(),IF('Table'[Invoice Amount]=highestinvoice&&'Table'[Account Owner Role]="Sales","Primary","Ignore"),IF('Table'[Invoice Amount]=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number]))),"Primary","Ignore"))
Result:
and here is sample pbix file, please try it
Best Regards,
Lin
You are a legend. Thank you so much, it works perfectly.
If I may ask another question, I might need to open another thread though but in case you can assist I would like to take the chance to ask.
is there anyway that I can add this to the actual query? I would like to exclude all the "ignore" from my final data table.
is this possible at all.
hi, @Anno2019
You could use this formula to create a new column as below:
Primary File Number line TAG = var countsales=CALCULATE(COUNTA('Table'[Account Owner Role]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return var highestinvoice=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number])&&'Table'[Account Owner Role]="Sales")) return IF(countsales<>BLANK(),IF('Table'[Invoice Amount]=highestinvoice&&'Table'[Account Owner Role]="Sales","Primary","Ignore"),IF('Table'[Invoice Amount]=CALCULATE(MAX('Table'[Invoice Amount]),FILTER('Table','Table'[File Number]=EARLIER('Table'[File Number]))),"Primary","Ignore"))
Result:
and here is sample pbix file, please try it
Best Regards,
Lin
You are a legend. Thank you so much, it works perfectly.
If I may ask another question, I might need to open another thread though but in case you can assist I would like to take the chance to ask.
is there anyway that I can add this to the actual query? I would like to exclude all the "ignore" from my final data table.
is this possible at all.
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 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |