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 Good People PBI,
I needed to create a new table based on calculation from Table 1 My table looked like this
Table 1:
IDNO | Store_Number | Slow |
AA1 | S1 | y |
AA1 | S2 | n |
AA1 | S3 | y |
AA1 | S4 | y |
AA1 | S5 | n |
AA1 | S6 | y |
Because of slow offloading at stores other stores get affected and hence a new coulmn needs to be created as below showing the problem causing stores .For example : S4 is affected because of slow offloading at s1 and s3 as both these storesd are marked as "y"
Table 2:
IDNO | Store_Number | Affected by |
AA1 | S2 | S1 |
AA1 | S3 | S1 |
AA1 | S4 | S1 |
AA1 | S4 | S3 |
AA1 | S5 | S1 |
AA1 | S5 | S3 |
AA1 | S5 | S4 |
AA1 | S6 | S1 |
AA1 | S6 | S3 |
AA1 | S6 | S4 |
with the help of PBI community the table 2 was created with the code :
Table2 =
GENERATE (Table1,SELECTCOLUMNS (CALCULATETABLE (DISTINCT ( Table1[Store_Number] ),Table1[Index] < EARLIER(Table1[Index] ),Table1[Slow] = "y",ALLEXCEPT ( Table1, Table1[IDNO] )),"Affected by", Table1[Store_Number]))
But i am unable to establish a one to many connection from Table 1 to Table2 as the message reads a circular depenecy was detected . I have information in table 1 (such as row count for each store etc.,)that is missing in table 2 and i need to find it only by establishing this relationship. Any work around it that you guys know off? Thanks a lot
hi @Anonymous
Just try to adjust the formula as below:
Table2 =
GENERATE (
Table1,
SELECTCOLUMNS (
CALCULATETABLE (
DISTINCT ( Table1[Store_Number] ),
FILTER(Table1,[Index] < EARLIER ( Table1[Index] )&&
Table1[Slow ] = "y"&&Table1[IDNO]=EARLIER(Table1[IDNO]))
),
"Affected by", Table1[Store_Number]
)
)
Regards,
Lin
Im still facing the same issue when trying to establish the relation ship 😞
P.S: slow_offloading_Stops is nothing but table 2 and [identify] is a unique ID for each record in Table 1 to be mapped as one to many relationship
hi @Anonymous
this work in my side, and here is my sample pbix file, please try it,
and if you still have the problem, please share your sample pbix file and your expected output.
Regards,
Lin
@Anonymous Can you include index column as well in Table1?
Hi @Anonymous ,
Don't know what is the calculation you are trying to achieve but if you use this code as variable in a measure maybe you can calculate other values.
Has you can see below using this measure:
Measure =
VAR temp_table =
GENERATE (
'Table';
SELECTCOLUMNS (
CALCULATETABLE (
DISTINCT ( 'Table'[Store_Number] );
'Table'[Index] < EARLIER ( 'Table'[Index] );
'Table'[Slow ] = "y";
ALLEXCEPT ( 'Table'; 'Table'[IDNO] )
);
"Affected by"; 'Table'[Store_Number]
)
)
RETURN
COUNTX ( temp_table; 'Table'[Slow ] )
I'm abble to count the Y and no of the second table altough the initial code only has 4 y and 2 no:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |