Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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êsUser | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |