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.
I have a table with customers and their ordering method in each period. I need to be able to find a first occurence of concatenation of 'Customer' and 'Method' where method is M1. I have tried multiple DAX functions but can't seem to find the right one.
Customer | Method | Period | FirstMethodM1 (Result) |
C1 | M1 | 1 | X |
C1 | M2 | 2 | |
C1 | M1 | 3 | |
C1 | M1 | 4 | |
C2 | M2 | 1 | |
C2 | M2 | 2 | |
C2 | M1 | 3 | X |
C2 | M1 | 4 | |
C2 | M2 | 5 | |
C2 | M3 | 6 | |
C3 | M1 | 2 | X |
C3 | M2 | 3 | |
C3 | M3 | 4 | |
C4 | M1 | 3 | X |
C4 | M2 | 4 | |
C4 | M3 | 5 |
Thank you!
Solved! Go to Solution.
Hello @Cyberguy23
you should be able to di tlike this:
IF( CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period], "X" )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
IF( AND(
CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period],
Table1[Period] = "M1"
), "X" )
yes, it should be:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hello @Cyberguy23
you should be able to di tlike this:
IF( CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period], "X" )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
I tested with a broader data set and it seems to work but not in all the cases. For example it picks up and marks as "X" some other method that happen in the same period. This case is also possible.
The combination C1+M3+2 should not be marked as "X". Hopefully it is just an additional filter that is required.
Customer | Method | Period | FirstPeriodM1 |
C1 | M2 | 1 | |
C1 | M1 | 2 | X |
C1 | M3 | 2 | X |
C1 | M1 | 3 |
IF( AND(
CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period],
Table1[Period] = "M1"
), "X" )
yes, it should be:
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Livio, thank you for your help. The initial questioned that I asked was answered by you in the previous email. But as I build more measure I need some more attributes. The "M1Customer" field should contain for customers that have ever ordered with method M1. See the table below.
As well as I need a "Combined Method" set for a customer that concatenates all "Method" values sorted alphabetically within a customer and displays the result in every row of a customer.
Customer | Method | Period | FirstPeriodM1 | M1Customer | Combined Method |
C1 | M2 | 1 | X | M1 & M2 & M3 | |
C1 | M1 | 2 | X | X | M1 & M2 & M3 |
C1 | M3 | 2 | X | M1 & M2 & M3 | |
C1 | M1 | 3 | X | M1 & M2 & M3 | |
C2 | M2 | 1 | M1 & M2 & M3 & M4 | ||
C2 | M3 | 2 | M1 & M2 & M3 & M4 | ||
C2 | M4 | 3 | M1 & M2 & M3 & M4 | ||
C3 | M1 | 2 | X | X | M1 &M3 & M4 |
C3 | M3 | 3 | X | M1 &M3 & M4 | |
C3 | M4 | 4 | X | M1 &M3 & M4 |
i want below result
ID | Status | Result |
123 | Fullypaid | Fullypaid |
123 | 1 | Fullypaid |
123 | 2 | Fullypaid |
123 | 3 | Fullypaid |
124 | Fullypaid | Fullypaid |
124 | Fullypaid | Fullypaid |
124 | Fullypaid | Fullypaid |
125 | 1 | Non Fullypaid |
125 | 2 | Non Fullypaid |
125 | 3 | Non Fullypaid |
126 | 1 | Fullypaid |
126 | 2 | Fullypaid |
126 | Fullypaid | Fullypaid |
Hi,
Write this calculated column formula
Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Status]="Fullypaid"))>0,"Fully paid","Not fully paid")
Hope this helps.
For the M1 column try this:
M1 Customer = IF( NOT( ISEMPTY( CALCULATETABLE( Data, ALLEXCEPT( Data, Data[Customer] ), Data[Method ] = "M1" ) ) ), "X" )
and for the other, try:
Column = VAR MethodsTable = CALCULATETABLE ( VALUES ( Data[Method ] ), ALLEXCEPT ( Data, Data[Customer] ) ) VAR RankedMethods = ADDCOLUMNS ( MethodsTable, "Value", RANKX ( MethodsTable, [Method ],, ASC ) ) VAR Test = CONCATENATEX ( NATURALINNERJOIN ( RankedMethods, GENERATESERIES ( 1, COUNTROWS ( MethodsTable ) ) ), [Method ], " & " ) RETURN Test
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
For the "Combined Method" I used the following formula which work for me but I also need to only get concatenation of distinct values.
CombinedMethod = CONCATENATEX ( FILTER (Table1, Table1[Customer] = EARLIER(Table1[Customer])), Table1[Method], ", ",Table1[Method],ASC )
Hello @Cyberguy23
For the M1 Column, try:
M1 Customer = IF( NOT( ISEMPTY( CALCULATETABLE( Data, ALLEXCEPT( Data, Data[Customer] ), Data[Method ] = "M1" ) ) ), "X" )
And for the other:
Column = VAR MethodsTable = CALCULATETABLE ( VALUES ( Data[Method ] ), ALLEXCEPT ( Data, Data[Customer] ) ) VAR RankedMethods = ADDCOLUMNS ( MethodsTable, "Value", RANKX ( MethodsTable, [Method ],, ASC ) ) VAR Test = CONCATENATEX ( NATURALINNERJOIN ( RankedMethods, GENERATESERIES ( 1, COUNTROWS ( MethodsTable ) ) ), [Method ], " & " ) RETURN Test
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
You are geniuos! Thankyou for your help. Both formulas worked.
Livio, if you could help me with the last new column please.
I need a column "FirstPeriodM1All" that is populated with a period number where M1 was first found per customer. The sample data will look like below. The logic is similar that is in the "FirstPeriodM1" with the exception that all customer records are now populated with a corresponding period number (whole Number)
Customer | Method | Period | FirstPeriodM1 | M1Customer | Combined Method | FirstPeriodM1All |
C1 | M2 | 1 | X | M1 & M2 & M3 | 2 | |
C1 | M1 | 2 | X | X | M1 & M2 & M3 | 2 |
C1 | M3 | 2 | X | M1 & M2 & M3 | 2 | |
C1 | M1 | 3 | X | M1 & M2 & M3 | 2 | |
C2 | M2 | 1 | M1 & M2 & M3 & M4 | |||
C2 | M3 | 2 | M1 & M2 & M3 & M4 | |||
C2 | M4 | 3 | M1 & M2 & M3 & M4 | |||
C3 | M1 | 2 | X | X | M1 &M3 & M4 | 2 |
C3 | M3 | 3 | X | M1 &M3 & M4 | 2 | |
C3 | M4 | 4 | X | M1 &M3 & M4 | 2 |
Hello @Cyberguy23
You just need to leverage the first formula I posed, and use only this part:
CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi, this is great. Exactly what I need. Thanks for all your help.
This is great, thank you!
Thank you, it worked.
Hi Folks,
I have same structure but unable to build the logic for the same.
In my case i need to show distinct value in table visual example
unique id signvalue variation
1 m1 01
1 m2 02
1 m1 00
2 m1 01
2 au 00
2 m2 01
3 m2 00
3 m1 00
So basially when for one unique id if sign value is m1 and m2 i need to always show m1 by default. if m1 not present then only need to show m2. also if one unique id has 3 variation then condition is to display all 3 variation.
result would be
1 m1 01
1 m2 02
1 m1 00
2 m1 01
2 m2 01
3 m1 00
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |