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've gone thorugh similar posts but couldn't resolve my issue. I want to create a new column with the following statement:
MAX Split BU 2 = IF((CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%])="0",MAXX(ALLEXCEPT(CDL_SVCS_Proj_Practice_Prod_Split,[Product Split %])=1,""))
However, it doesn't seem to work with a syntax like this. What I want to achieve here is this column to brings in the max value for every project ID where the Product Split % is NOT equal to 1.
What would be an alternative in order to achieve what this statement intends to do ?
Any help would be much appreciated.
Solved! Go to Solution.
Try this Column
Max Split BU 2 = VAR myMax = CALCULATE ( MAX ( TableName[Product Split %] ), FILTER ( ALLEXCEPT ( TableName, TableName[Project ID] ), TableName[Top BU 100%] = "SPLIT BU" ) ) RETURN CALCULATE ( FIRSTNONBLANK ( TableName[Top BU], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Project ID] ), TableName[Product Split %] = mymax ) )
Give this a shot
MAX Split BU 2 = IF ( TableName[TopBU100%] = 0, CALCULATE ( MAX ( TableName[ColumnName] ), FILTER ( ALLEXCEPT ( TableName, TableName[ProjectID] ), TableName[Product Split %] = 1 ) ) )
Thanks for following up Zubair_Muhammad
This looks better than my initial attempt but still doesn't achieve what I want. Let me try visualize it better, I've slightly changed some arguments but we can focus on the following,. So, as you can see in the attached table below
we have multiple products for the same project ID. I want to populate the MAX Split BU 2 column with the TOP BU for the MAX Product Split %, when the TOP BU 100% = "SPLIT BU".
Therefore, in this example, MAX Split BU 2 column should be populated with AO, since the MAX Split % for this project for which the Top BU 100% is "SPLIT BU" is 0.4.
Perhaps I want all three MAX Split BU 2 lines to be populated with AO. I will then use this column values in a LOOKUP, so as when the project ID is found in another table (with an established relationship) to return the MAX Split BU 2 if the aTOP BU 100% = "SPLIT BU". This is the next step, for now I have to make the first bit working 🙂
So you need the Top BU name
Please could you upload the file in onedrive or googledrive and share the link here
Thanks @Zubair_Muhammad
I think I'm close. I changed the query to:
MAX Split BU 2 = IF ( CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%] = "SPLIT BU", CALCULATE ( MAX ( CDL_SVCS_Proj_Practice_Prod_Split[Top BU] ), FILTER ( ALLEXCEPT ( CDL_SVCS_Proj_Practice_Prod_Split, CDL_SVCS_Proj_Practice_Prod_Split[Project ID] ), CDL_SVCS_Proj_Practice_Prod_Split[Top BU 100%] = "SPLIT BU" ) ) )
But it returns the wrong value. Instead of AO which is the TOP BU for the MAX Split % (which is 0.4) it returns SM instead of AO
Try this Column
Max Split BU 2 = VAR myMax = CALCULATE ( MAX ( TableName[Product Split %] ), FILTER ( ALLEXCEPT ( TableName, TableName[Project ID] ), TableName[Top BU 100%] = "SPLIT BU" ) ) RETURN CALCULATE ( FIRSTNONBLANK ( TableName[Top BU], 1 ), FILTER ( ALLEXCEPT ( TableName, TableName[Project ID] ), TableName[Product Split %] = mymax ) )
thank you very much @Zubair_Muhammad. It works perfect 🙂
This is a bit too technical for me but I think I get the logic.
I created a dummy example to test it.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |